Forum Discussion

glee1207's avatar
glee1207
Regular Visitor
3 years ago
Solved

Previous Years data

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

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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

     

     

6 Replies

    • glee1207's avatar
      glee1207
      Regular Visitor
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

        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