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.
Hi All,
We are trying to calculate change% from the average over the time period selected.
Data set -
Dec- 5
Jan - 4
Feb - 6
if user selects "last 3 months"
sum - 15
avg - 5
change% - %change of the last value(6) from the average (5)
=20%
How to calcuate last value based on the user selection? So if user selects "last 3 year", need to show sum in that period & for delta calculation need "SUM IN LAST 1 YEAR" also.
Please suggest if any other slicer/workaround can help here.
Solved! Go to Solution.
Hi @Anonymous ,
I'm afraid there is no DAX function meeting your requirements.
But you can get the Range of selected date with MAX() and MIN() function.
Or, you can try to create slicers which work like relative date slicer by yourself. For specific operations, you can refer to the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
try this:
WasWeek =
IF(
YEAR ( [Fecha] ) <= YEAR ( TODAY () )
;IF(
MONTH ( [Fecha] ) <= MONTH ( TODAY () );
IF(
WEEKDAY( ( [Fecha] ) < WEEKDAY( ( TODAY () )));"Yes";"No"
)
;"No")
;"No")
You can take advantage of rolling formula, Change no and period as per need. use with the Calendar table. get you day, moth year slicer from date tbale
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Day))
Rolling 4 week same week day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-28,Day),filter(Date, WeekDay('Date'[Date])=max( WeekDay('Date'[Date]))))
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Thanks Amit for the help. I do have a calendar table in my model.
To determine which formula to use, how do we determine if week or year or month has been selected ?
Hi @Anonymous ,
I'm afraid there is no DAX function meeting your requirements.
But you can get the Range of selected date with MAX() and MIN() function.
Or, you can try to create slicers which work like relative date slicer by yourself. For specific operations, you can refer to the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
How do you create the table calendar and week/month/calendar ?
Hi @Anonymous ,
You can find it in the attached PBIX file above.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2020, 2, 29 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"WeekNum", WEEKNUM([Date],1)
)
Week/Month/Year =
UNION (
SUMMARIZE ( 'Calendar', 'Calendar'[YearNum of All Years], "Years", "Years" ),
SUMMARIZE ( 'Calendar', 'Calendar'[MonthNum of All Years], "Months", "Months" ),
SUMMARIZE ( 'Calendar', 'Calendar'[WeekNum of All Years], "Weeks", "Weeks" )
)
Best Regards,
Icey
Hi @Icey
Relative date slicer is a smart solution, I was looking for something similar. Thanks, that does help.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |