Forum Discussion
Percentage difference between dynamic date range
Hi Memphis28 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a Date Slicer table and a measure.
Date Slicer = CALENDAR(FIRSTDATE('TeamJ'[date]),LASTDATE('TeamJ'[date]))Measure =
var _slicer_min_date=MIN('Date Slicer'[Date])
var _slicer_max_date=MAX('Date Slicer'[Date])
var _min_value=CALCULATE(COUNTA('TeamJ'[case_id]),FILTER(ALLSELECTED('TeamJ'),'TeamJ'[date]=_slicer_min_date))
var _max_value=CALCULATE(COUNTA('TeamJ'[case_id]),FILTER(ALLSELECTED('TeamJ'),'TeamJ'[date]=_slicer_max_date))
RETURN DIVIDE(_min_value,_max_value,0)The range of the slicer at this point is 2023-2-1 through 2024-1-15, at which point the measure will count the number of cases on the minimum date (2023-2-1) / the number of cases on the maximum date (2024-1-15).
If you change the slicer range, then the measure will also change automatically.
(3) Then we can create another measure.
Measure 2 =
var _slicer_min_date=MIN('Date Slicer'[Date])
var _min_value=CALCULATE(COUNTA('TeamJ'[case_id]),FILTER(ALLSELECTED('TeamJ'),'TeamJ'[date]=_slicer_min_date))
var _report_value=COUNTA('TeamJ'[case_id])
RETURN DIVIDE(_min_value,_report_value,0)
For learning about measure values and dax, you can refer to the:
Measures in Power BI Desktop - Power BI | Microsoft Learn
Learn DAX basics in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the resolution, i have worked through it and as far as my powerbi DAX knowledge goes, nothing seems to be incorrect however i am get a BLANK value on the scorecard and the second measure produces no results in a table or matrix.
I have broken down each part of the measure you wrote and understand each bit indiviudally and it all makes perfect sense. Not only that PowerBi hasn't highlighted any issues. I tried doing it without the Date slicer and instead just used the date of the report directly but this gave me the same result. Next i removed any other slicers to see if that was impacting the data but again i got the same BLANK card.
Could something else in the report be interfering with the outcome other than another slicer?
Again, having broken down the measure in to its constituent parts it should, work but for no obvious reason, it isn't. Any other steer or guidance whould be hugely appreciated.
Thanks
Memphis
Hi Memphis28 ,
Can you check the model relationship? Please make sure that there is no relationship between the slicer table and the data table.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.