topic Re: Current Fiscal Month TRUE FALSE in DAX Commands and Tips

Current Fiscal Month TRUE FALSE

joshua1990 — Wed, 29 Jul 2020 05:40:30 GMT

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 ()

Re: Current Fiscal Month TRUE FALSE

Fowmy — Wed, 29 Jul 2020 05:57:37 GMT

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

Re: Current Fiscal Month TRUE FALSE

amitchandak — Wed, 29 Jul 2020 06:08:13 GMT

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]
)

Re: Current Fiscal Month TRUE FALSE

joshua1990 — Wed, 29 Jul 2020 18:31:25 GMT

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

Re: Current Fiscal Month TRUE FALSE

Fowmy — Thu, 30 Jul 2020 07:17:56 GMT

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

Re: Current Fiscal Month TRUE FALSE

Anonymous — Wed, 29 Jul 2020 18:55:15 GMT

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

Re: Current Fiscal Month TRUE FALSE

joshua1990 — Wed, 29 Jul 2020 19:10:59 GMT

Anonymous 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.

Re: Current Fiscal Month TRUE FALSE

Fowmy — Wed, 29 Jul 2020 19:21:49 GMT

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

Re: Current Fiscal Month TRUE FALSE

Anonymous — Thu, 30 Jul 2020 07:04:27 GMT

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

Re: Current Fiscal Month TRUE FALSE

joshua1990 — Thu, 30 Jul 2020 17:21:50 GMT

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

Re: Current Fiscal Month TRUE FALSE

joshua1990 — Thu, 30 Jul 2020 17:51:48 GMT

Anonymous : Thanks! Your approach is working!

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

Re: Current Fiscal Month TRUE FALSE

Anonymous — Fri, 31 Jul 2020 03:47:52 GMT

hi joshua1990 Glad it's working.

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

Thanks.

Re: Current Fiscal Month TRUE FALSE

joshua1990 — Fri, 31 Jul 2020 06:01:13 GMT

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)

Re: Current Fiscal Month TRUE FALSE

Anonymous — Fri, 31 Jul 2020 07:40:24 GMT

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