Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
54 | |
26 | |
20 | |
15 | |
11 |
User | Count |
---|---|
77 | |
62 | |
44 | |
18 | |
12 |