Previous Years data

[glee1207]

Hi,

I have the data of students with columns "Applications Sent Date" (ASD) and "Course Start Date" (CSD).

Now for my visualisation, I would like to see a comparison side by side for the selected period for both the date ranges using individual slicers and the same data for the previous year.

Something like this:

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[glee1207]

Hi @lbendlin ,

Thank you getting back to me.

I have attached a dummy data for your reference as I cannot share the actual data, but this should give you a fair idea of what I need.

Dummy.xlsx

Alongwith the data, there are 2 pivots showing what is needed.

I want a DAX formula, in which if I out a date filter for 2023 in the visual, it can take the data for the same range for 2022 and accordingly calculate the data.

Thanks and Regards

glee

[lbendlin · Accepted solution]

First step is to add a calendar to your data model. It will link to the application date and also to the course start date.

Then add a date slicer from the calendar table.

Next define your measures.

Applications = COUNTROWS(Courses)

Started = CALCULATE(COUNTROWS(Courses),USERELATIONSHIP(Courses[Course Start Date],Dates[Date]))

Applications prior period = 
var mind = min(Dates[Date])
var maxd = max(Dates[Date])
return calculate(countrows(Courses),datesbetween(Dates[Date],edate(mind,-12),edate(maxd,-12)))

Started prior period = 
var mind = min(Dates[Date])
var maxd = max(Dates[Date])
return calculate(countrows(Courses),datesbetween(Dates[Date],edate(mind,-12),edate(maxd,-12)),USERELATIONSHIP(Courses[Course Start Date],Dates[Date]))

This will give you maximum flexibility with the definition of your date range.

see attached

[glee1207]

Hi @lbendlin 

Many thanks for the proposed solution, however, there are some problems with that.

As I will be needing 2 slicers not one for both the date columns (Application Sent Date (ASD) and Course Start Date (CSD)). 

Also, there is a problem with the max function being used which has come to my attention today.

Let's say, this year the CSD is 05-Sep-2023, the max function will take 05-Sep-2023 and accordingly the VAR function will produce: 05-Sep-2022.

But if last year the max CSD were to be let's say 10-Oct-2022, it will not take the date into account, even if my filter is set to CSD from 01-July-2023 to 31-Dec-2023.

And I would also like to use Count and DistinctCount to count Applications and Applicants.

Hope this makes sense.

Thanks and Regards

Glee.

[lbendlin]

I think this becomes a deeper discussion if what you are trying to achieve makes sense from an "apples to apples" perspective. I can't help you if I don't understand (and agree with) the applied rules.

[glee1207]

Hi @lbendlin 

Thanks for your help.

I have marked it as Solution as it gave me another idea using your solution.

I created 2 date tables one for each date and then used dax formula you mentioned above using both the dates.

Thanks and Regards

Glee