topic Re: Percentage difference between dynamic date range in DAX Commands and Tips

Percentage difference between dynamic date range

Memphis28 — Wed, 24 Jan 2024 12:48:45 GMT

Afternoon

I have had a look on the forum and, depsite the wealth of knoweldge i can't seem to find the answer to my issue so hoping someone can point me in the right direction.

I think it is a simple ask but i am relatively new and can't seem to get my head around it.

I want to create a measure that will calculate the percentage difference between two values (this will be a total count value of cases/tickets). I want to be be able to change the date range using a slicer and have the percentage dynamically change wiht it.

So case count on the MIN date is first value and the case count on the MAX date is the second value. I tried using the quick measures tool but it forces your hand in to choosing a specific date as you can see below. Is there a way to adapt to function code to make it dynamic?

The outcome being two things, a scorecard that shows the percentage difference between the date range (as selcetd by a slicer) and a table with the date of each report and the difference between the MIN date and the date of the report (see bottom screen shot)

I am keen to learn so any guidance on how to do it but also why it is done that way is hugely appreciated.

Count of CASE_ID % difference from 27 October 2023 =

VAR __BASELINE_VALUE =

CALCULATE(

COUNTA('TeamJ'[CASE_ID]),

'TeamJ'[Date of Report] IN { DATE(2023, 10, 27) }

)

VAR __MEASURE_VALUE = COUNTA('TeamJ'[CASE_ID])

RETURN

IF(

NOT ISBLANK(__MEASURE_VALUE),

DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

)

Below is showing the differnece from the 27th of Oct no matter the date selected using the slicer.
FYI the slicer uses the date of the report data

Re: Percentage difference between dynamic date range

Anonymous — Thu, 25 Jan 2024 02:32:31 GMT

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.

Re: Percentage difference between dynamic date range

Memphis28 — Thu, 25 Jan 2024 09:46:29 GMT

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

Re: Percentage difference between dynamic date range

Anonymous — Fri, 26 Jan 2024 08:46:27 GMT

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.

Re: Percentage difference between dynamic date range

Memphis28 — Mon, 29 Jan 2024 12:15:45 GMT

Afternoon
I haven't had chances to try this suggestion yet, i will do as soon as i can and report back.
I did figure a quick and dirty way of getting some of what i needed but it isn't the final solution. I created two measures, one that found the Value on the FIRSTDATE in the Date table and one that found the LASTDATE in the date table and then wrote a 3rd measure to DIVIDE the two. Not elegent or efficient but it worked on the day.

PercentSlicerDiffMin = CALCULATE(COUNTA(TeamJ[CASE_ID]), FIRSTDATE(TeamJ[Date of Report]))

PercentSlicerDiffMin = CALCULATE(COUNTA(TeamJ[CASE_ID]), LASTDATE(TeamJ[Date of Report]))

PercentDivide = DIVIDE([PercentSlicerDiffMin]-[PercentSlicerDiffMax],[PercentSlicerDiffMax],0)

I still need a better solution that wll show the changes in a table so i will try and pull some sample data and a streamlined version of PBIX later today and share with you.
Again, the help is appreciated. Thank you