计算每个会计月份的剩余天数

问题描述：

基于此帖子，我正在构建一个财政日历：

Based on this Post I am building a fiscal calendar:

以下Dax会生成DAX日历：

The following Dax generates a DAX Calendar:

Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2016; 1; 1 ); DATE ( 2025; 12; 31 ) )
RETURN
    GENERATE (
        BaseCalendar;
        VAR BaseDate = [Date]
        VAR Year =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR WeekNumber =
            WEEKNUM ( BaseDate )
        VAR FWeek =
            WEEKNUM ( BaseDate; 21 )
        RETURN
            ROW (
                "Day"; BaseDate;
                "Year"; Year;
                "Month Number"; MonthNumber;
                "Month"; FORMAT ( BaseDate; "mmmm" );
                "Year Month"; FORMAT ( BaseDate; "yyyy-mm" );
                "Day of Week"; FORMAT ( BaseDate; "dddd" );
                "Day of Week Short"; FORMAT ( BaseDate; "ddd" );
                "Week"; WeekNumber;
                "Year-Week"; Year & "-" & WeekNumber;
                "Fiscal Week"; FWeek;
                "Fiscal Month Short"; SWITCH (
                    TRUE ();
                    FWeek IN { 1; 2; 3; 4 }; "Jan";
                    FWeek IN { 5; 6; 7; 8 }; "Feb";
                    FWeek IN { 9; 10; 11; 12; 13 }; "Mar";
                    FWeek IN { 14; 15; 16; 17 }; "Apr";
                    FWeek IN { 18; 19; 20; 21 }; "May";
                    FWeek IN { 22; 23; 24; 25; 26 }; "Jun";
                    FWeek IN { 27; 28; 29; 30 }; "Jul";
                    FWeek IN { 31; 32; 33; 34 }; "Aug";
                    FWeek IN { 35; 36; 37; 38; 39 }; "Sep";
                    FWeek IN { 40; 41; 42; 43 }; "Oct";
                    FWeek IN { 44; 45; 46; 47 }; "Nov";
                    FWeek IN { 48; 49; 50; 51; 52; 53 }; "Dec"
            )
    )

现在我需要一个列，该列显示我每个日期每个会计月份的剩余天数。
我建议我需要第一列，该列累积每月的工作日。
然后，我需要第二列从对应月份的总和中减去此值。

Now I need a column that shows me remaining days for the respective fiscal month for every date. I would suggest I need a first column, that accumulates the working days per month. Then I need a second column that subtract this value from the total sum for the corresponding month.

如何在同一日历中将其添加到日历中

How would you add build that into the calendar in the same approach provided above?

答

取决于您定义的工作日。我想它是星期一至星期五
您需要在上面的DAX中添加新列以返回1或0

Depending on what you define as a working day. I'm presuming it is Mon-Fri You need a new column added to your above DAX to return a 1 or 0

IsWeekDay = SWITCH(TRUE(), WEEKDAY(Calendar[Date], 2) <= 5, 1, 0)

因此，周一至周五它将返回1，其他则返回0。
接下来为您的月份开始日期添加一列，在此示例中，我使用的是日历月：

So it will return 1 for Mon-Fri, 0 for anything else. Next add a column for your month start date, I'm using the Calendar Month in this example:

MonthStart = STARTOFMONTH(Calendar[Date])

对于日期04/04/2020，它将返回日期为01/04/2020

For a date of 04/04/2020, it will return a date of 01/04/2020

下一个新列应使用EARILER总结工作日

The next new column should sum up the working days using EARILER

Working Days = 
CALCULATE ( SUM('Calendar'[IsWeekDay]),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] >= EARLIER ( 'Calendar'[Date] )
            &&  EARLIER('Calendar'[MonthStart]) = STARTOFMONTH('Calendar'[Date])
    )
)

您必须针对您的财政月开始日期进行调整

You'll have to adjust it for your Fiscal Month Start Date

计算每个会计月份的剩余天数

相关推荐