Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
"Current Fiscal Month", IF (
AND ( FiscMonth = MONTH ( TODAY () ), FiscYear = YEAR ( TODAY () ) ),
TRUE (),
FALSE ()
Solved! Go to 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
@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
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
@Anonymous : Thanks! Your approach is working!
Is it possible to add your approach into a RETURN ( ROW Approach?
hi @joshua1990 Glad it's working.
I don't understand what do you mean by RETURN ROW approach. Can you share an example?
Thanks.
@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)
Dear guys,
thanks you so much for your help!
It is still not working.
Please find attached an excel file with our fiscal calendar.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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]
)
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
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
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |