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.
Hey all,
I'm trying to do calculations for a time report but hitting a snag because we report time from the 25th of each month to the 24th of the next, rather than on the calendar months. So for January's time report we actually need to pull Dec. 25th-Jan 24th. Is there a way to create a custom month table so that in the slicer when I pick January it automatically pulls from the 25th of the previous month.
The relative date filter isn't working for my matrix currently because it is pulling ALL dates instead of just the ones I choose.
Solved! Go to Solution.
@cedmiston - You could create additional columns as:
NewMonthNum =
IF (
DAY ( 'Table'[Date] ) < 25,
MONTH ( 'Table'[Date] ),
MOD ( MONTH ( 'Table'[Date] ) + 1, 12 )
)
and
SwitchMonthName =
SWITCH(
TRUE(),
'Table'[NewMonthNum] = 1, "Jan",
'Table'[NewMonthNum] = 2, "Feb",
"etc"
)
Then you could use this as your slicer.
Proud to be a Super User!
Try create a month column, should be something like this:
Month.NO =
VAR Monthno_ =SWITCH (TRUE(),
'Table'[Date].[Day]<25, MONTH([Date])-1,
'Table'[Date].[Day]>=25,MONTH('Table'[Date]))
RETURN IF(Monthno_=0, MONTH(CALCULATE(MAX([Date]),FILTER('Table',[Date].[Year]=EARLIER('Table'[Date].[Year])-1))),Monthno_)
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good morning cedmiston,
Hereby my Power query solution:
- I've created a custom function "SetCustomMonth" that retunrs the month number based on another start day (25 instead of 1 in your example)
- you can invoke this function in Power Query to create a new column for your month.
/*
Return the month number based on a custom start day of month
For example, if inputStartDayofMonth = 25 and inputDate = 1/17/2019 then it will return 1 (January)
For example, if inputStartDayofMonth = 25 and inputDate = 1/26/2019 then it will return 2 (February)
Parameter
inputDate: Date for which the month needs to be defined.
inputStartDayofMonth: the day of the month that is considered as the first day of the month.
Disclaimer:
- the date column used must always have a value otherwise an error will be thrown
- This function won't work yet with a inputStartDayofMonth of 31
homepage: <>
*/
let
SetCustomMonth = (inputDate as date, inputStartDayofMonth as number) as number =>
let
//get day in Gregorian calendar month
DayofMonth = Date.Day(inputDate),
//Year input date
YearInputDate = Date.Year(inputDate),
//Month input date
MonthInputDate = Date.Month(inputDate),
//Month input date + 1 month
MonthInputDatePlus1mth = Date.Month(Date.AddMonths(inputDate,1)),
//DayofMonthBased is the difference in day between the inputDate and the start periode
CustomMonth =
if
DayofMonth >= inputStartDayofMonth
then
MonthInputDatePlus1mth
else
MonthInputDate
in
CustomMonth
in
SetCustomMonth
Did I answer your question, please mark my post as a solution
If you liked my solution, please give it a thumbs up.
Thank you and keep up the good job!
Kind regards,
Lohic Beneyzet
Try create a month column, should be something like this:
Month.NO =
VAR Monthno_ =SWITCH (TRUE(),
'Table'[Date].[Day]<25, MONTH([Date])-1,
'Table'[Date].[Day]>=25,MONTH('Table'[Date]))
RETURN IF(Monthno_=0, MONTH(CALCULATE(MAX([Date]),FILTER('Table',[Date].[Year]=EARLIER('Table'[Date].[Year])-1))),Monthno_)
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@cedmiston - You could create additional columns as:
NewMonthNum =
IF (
DAY ( 'Table'[Date] ) < 25,
MONTH ( 'Table'[Date] ),
MOD ( MONTH ( 'Table'[Date] ) + 1, 12 )
)
and
SwitchMonthName =
SWITCH(
TRUE(),
'Table'[NewMonthNum] = 1, "Jan",
'Table'[NewMonthNum] = 2, "Feb",
"etc"
)
Then you could use this as your slicer.
Proud to be a Super User!
This worked great thank you!
There was one bug in that the end of November (25-30) showed up as 0 instead of 1-12, but I just added this to the switch formula as well to populate as our "December".
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |