Current Fiscal Month TRUE FALSE

[joshua1990]

Hello everyone!

 I would like to add a binaer column into my fiscal calendar to get a TRUE/FALSE, if the date is in the current fiscal month.

How would you do that?

The fiscal months have specific start- and end dates.

The following approach is not working:

"Current Fiscal Month", IF (
AND ( FiscMonth = MONTH ( TODAY () ), FiscYear = YEAR ( TODAY () ) ),
TRUE (),
FALSE ()

[Anonymous · Accepted solution]

@joshua1990  You can try using ADDCOLUMNS function like this.

Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2018, 12, 31 ), DATE ( 2022, 01, 03 ) )
VAR FiscalCalendar = 
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR IsWorkingDay =
            IF ( WEEKDAY ( BaseDate, 2 ) > 5, FALSE (), TRUE () )
        VAR WorkingDayFactor =
            IF ( IsWorkingDay = TRUE (), 0, 1 )
        VAR Year =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR WeekNumber =
            WEEKNUM ( BaseDate ) 
        VAR FiscWeek =
            WEEKNUM ( BaseDate, 21 )
        VAR FiscWeekTxt =
            FORMAT(WEEKNUM ( BaseDate, 21 ), "00")
        
        VAR FiscYear =
            IF (
                FiscWeek < 5
                    && WeekNumber > 50,
                Year + 1,
                IF ( FiscWeek > 50 && WeekNumber < 5, Year - 1, Year )
            )
        RETURN
            ROW (
                "Year", Year,
                "Month Number", MonthNumber,
                "Month", FORMAT ( BaseDate, "mmmm" ),
                "Year Month", FORMAT ( BaseDate, "yyyy-mm" ),
                "Day of Week", FORMAT ( BaseDate, "dddd" ),
                "Day of Week Number", WEEKDAY ( BaseDate, 2 ),
                "Day of Week Short", FORMAT ( BaseDate, "ddd" ),
                "IsWorkingDay", IF ( IsWorkingDay = TRUE (), 1, 0 ),
                "Week", WeekNumber,
                "Year-Week", Year & "-" & WeekNumber,
                "Fisc Week", FiscWeek,
                "Fisc Year-WK", FiscYear & "-" & FiscWeekTxt
            )
    )
VAR RESULT = ADDCOLUMNS
                    (
                    FiscalCalendar
                    , "IsCurrentFiscalMonth"
                    , VAR td = TODAY ()
                        VAR FMY = [Year Month]
                        VAR FilteredTable =
                                FILTER (FiscalCalendar , [Year Month] =  FMY )
                         //Fiscal Month Start Date
                        VAR FMSD =
                            MINX ( FilteredTable, [Date] )
                        // Fiscal Month End Date
                        VAR FMED =
                            MAXX ( FilteredTable, [Date])
                        RETURN 
                            SWITCH ( TRUE (), td >= FMSD && td <= FMED, TRUE (), FALSE () )
                    )
RETURN RESULT

I have modified your DAX expression becuause few of the variables were missing.

Anyways you can use ADDCOLUMNS function as shown above.

Thanks and Regards

[Anonymous]

Hi @joshua1990 

Please try this.

IsCurrentFiscalMonth =
VAR td =
    TODAY ()
VAR FMY = Table[FiscalMonthYear]
VAR FilteredTable =
    FILTER ( ALL ( Table ), Table[FiscalMonthYear] = FMY )
 //Fiscal Month Start Date
VAR FMSD =
    MINX ( FilteredTable, Table[Date] )
// Fiscal Month End Date
VAR FMED =
    MAXX ( FilteredTable, Table[Date] )
RETURN
    SWITCH ( TRUE (), td >= FMSD && td <= FMED, TRUE (), FALSE () )

Hope this helps.

Thanks

[joshua1990]

@Anonymous : Thanks! Your approach is working!

Is it possible to add your approach into a RETURN ( ROW Approach? 

[Anonymous]

hi @joshua1990 Glad it's working.

I don't understand what do you mean by RETURN ROW approach. Can you share an example?

Thanks.

[joshua1990]

@Anonymous Thank you so much for your support.

I have build the calendar like following:

Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2018, 12, 31 ), DATE ( 2022, 01, 03 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR IsWorkingDay =
            IF ( WEEKDAY ( BaseDate, 2 ) > 5, FALSE (), TRUE () )
        VAR WorkingDayFactor =
            IF ( IsWorkingDay = TRUE (), 0, 1 )
        VAR Year =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR WeekNumber =
            WEEKNUM ( BaseDate ) 
        VAR FiscWeek =
            WEEKNUM ( BaseDate, 21 )
        VAR FiscWeekTxt =
            FORMAT(WEEKNUM ( BaseDate, 21 ), "00")
        VAR FiscMonth =
            SWITCH (
                TRUE (),
                JnJWeek IN { 1, 2, 3, 4 }, 1,
                JnJWeek IN { 5, 6, 7, 8 }, 2,
                JnJWeek IN { 9, 10, 11, 12, 13 }, 3,
                JnJWeek IN { 14, 15, 16, 17 }, 4,
                JnJWeek IN { 18, 19, 20, 21 }, 5,
                JnJWeek IN { 22, 23, 24, 25, 26 }, 6,
                JnJWeek IN { 27, 28, 29, 30 }, 7,
                JnJWeek IN { 31, 32, 33, 34 }, 8,
                JnJWeek IN { 35, 36, 37, 38, 39 }, 9,
                JnJWeek IN { 40, 41, 42, 43 }, 10,
                JnJWeek IN { 44, 45, 46, 47 }, 11,
                JnJWeek IN { 48, 49, 50, 51, 52, 53 }, 12
            )
        VAR FiscYear =
            IF (
                FiscWeek < 5
                    && WeekNumber > 50,
                Year + 1,
                IF ( FiscWeek > 50 && WeekNumber < 5, Year - 1, Year )
            )
        RETURN
            ROW (
                "Year", Year,
                "Month Number", MonthNumber,
                "Month", FORMAT ( BaseDate, "mmmm" ),
                "Year Month", FORMAT ( BaseDate, "yyyy-mm" ),
                "Day of Week", FORMAT ( BaseDate, "dddd" ),
                "Day of Week Number", WEEKDAY ( BaseDate, 2 ),
                "Day of Week Short", FORMAT ( BaseDate, "ddd" ),
                "IsWorkingDay", IF ( IsWorkingDay = TRUE () && IsHoliday = FALSE (), 1, 0 ),
                "Week", WeekNumber,
                "Year-Week", Year & "-" & WeekNumber,
                "Fisc Week", FiscWeek,
                "Fisc Year-WK", FiscYear & "-" & FiscWeekTxt,
                "Fisc Month Short", SWITCH (
                    TRUE (),
                    FiscMonth IN { 1 }, "Jan",
                    FiscMonth IN { 2 }, "Feb",
                    FiscMonth IN { 3 }, "Mar",
                    FiscMonth IN { 4 }, "Apr",
                    FiscMonth IN { 5 }, "May",
                    FiscMonth IN { 6 }, "Jun",
                    FiscMonth IN { 7 }, "Jul",
                    FiscMonth IN { 8 }, "Aug",
                    FiscMonth IN { 9 }, "Sep",
                    FiscMonth IN { 10 }, "Oct",
                    FiscMonth IN { 11 }, "Nov",
                    FiscMonth IN { 12 }, "Dec"
                )
            )
    )

(It's just a snapshot of the whole code) 

[joshua1990]

Dear guys,

thanks you so much for your help!

It is still not working.

Please find attached an excel file with our fiscal calendar.

https://workupload.com/file/s9M34aWLhKu

[Fowmy]

@joshua1990 

Add as a new column:

IsCurrentMonth = 
IF(
    FORMAT(TODAY(),"yyyy-mmm") = FISCALCALENDAR[Fiscal Year-Month],
    TRUE(),
    FALSE()
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@joshua1990 , Try like

Month Type = Switch( True(),
[start date]<=Today() && [end date]>=Today(),"This Month" ,
[Month Name]
)

Month Type = Switch( True(),
Date([Date]) = eomonth(Today(),-1),"Last Month" ,
Date([Date])= eomonth(Today(),0),"This Month" ,
[Month Year]
)

[Fowmy]

@joshua1990 

Can you share some sample data and the expected result as data to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[joshua1990]

thanks for your reply @Fowmy 

Here is some sample data

Date	Fiscal Week	Fiscal Month	Fiscal Year	Fiscal Month Number	Fiscal Year-Month
30.12.2019	01	January	2020	01	2020-01
31.12.2019	01	January	2020	01	2020-01
01.01.2020	01	January	2020	01	2020-01

[harshnathani]

Hi @joshua1990 ,

Not very clear on the expected output.

But see if this helps.

Create a Calculated Column

Column = 
var _month = MONTH('Table'[Date])
var _year = YEAR('Table'[Date])

RETURN

IF(_month = 'Table'[Fiscal Month Number] && _year = 'Table'[Fiscal Year] , "True","False")

Regards,

Harsh Nathani

[joshua1990]

@harshnathani @Fowmy 

Thanks you so much for your support!

But how can your approach work?

I mean, I have specific start and end dates for every fiscal month.

January is not starting at 01.01.2019.

It starts at 30.12.2019.

I don't think standard functions like YEAR, MONTH will work with this specific structure.

Your approach is not working.

[Fowmy]

@joshua1990 

Your table already has a field that defined the fiscal year-month 

Fiscal Year-Month

So comparing the year and month of today's date should give you the correct result. Did you try? Share your data withthe  results if it doesn't work.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn