Users to select the date range of data to run calculations on the data

[SatyajitC]

Dear Team,

I have a scenario wherein we append historical data in excel file every week. So,  every week there may be changing values of the same attributes. The users of the power bi dashboard want to select any two date range out of all the weeks, one date as start date and another date as end date and to see the delta change between the dates. They want to be able to select any dates of their choice from the given list and see the calculations. Let me give an example data in a table below

Run Date	account_id	cohort	Amount
7/6/2023	CUST-0000018081	Good	1000
7/6/2023	CUST-0000018078	Great	500
7/6/2023	CUST-0000018105	Behind	2000
8/10/2023	CUST-0000018081	Behind	1200
8/10/2023	CUST-0000018078	Great	900
8/10/2023	CUST-0000018105	Great	2000
8/17/2023	CUST-0000018081	Great	1500
8/17/2023	CUST-0000018078	Great	1200
8/17/2023	CUST-0000018105	Good	2100

As you can see for the same account_ids, I have data for 3 different "Run Date". Now a user wants to select any one of these dates as Start Date and any other date as End Date and accordingly the dashboard should show delta calculations.

How can we achieve this dynamic date selection by user and run all the calculation based on the selection.

If Start Date = 7/6/2023 and End Date = 8/17/2023, then the count of "Great" accounts increased from 1 to 2.
However, if the Start Date = 8/10/2023 and End Date = 8/17/2023, then the count of "Great" accounts is flat. 2 vs 2.

Thanks in advance for sharing the ideas to achieve this requirement.

Regards,
Satyajit

[SatyajitC]

Thanks Marcel,
Probably I couldn't explain the requirement properly. The main requirement is to be able to chunk out those two dates (start date and end date as selected by the user) of data from the whole dataset and apply many more delta calculations between the Start date data and end date data. For example, the below matrix to be prepared with Start Date in row and End Date in column

We should be able to povide a delta view of change of each cohort count between the two selected dates. The main idea is to chunk out the data based on user selection in the filter and then play around with that data.

Hope I was able to explain this time.

Thanks,
Satya

[marcelsmaglhaes]

Hey @SatyajitC !

You solved your question? If not, maybe you can use something like that (if I understood your requirement)
Regards, 
Marcel

My_Measure = 
VAR __StartDate = SELECTEDVALUE(dcalendar[date_ini])
VAR __EndDate = SELECTEDVALUE(dcalendar[date_end])
RETURN
CALCULATE(COUNT(YOUR_COLUMN), TABLE[DATE] >= __StartDate  && TABLE[DATE] <= __EndDate)

[marcelsmaglhaes]

Hey @SatyajitC 

Do you have a dimension Date table? This dimension is the one you should use in your filter. Users can filter a period, like you said 8/10/2023 and 8/17/2023, and the measure (usign COUNT) it will count 2 for 0000018078 Great.

Regards,
Marcel