Cumulative Applications over LY Cumulative Applications

[Anonymous]

Hi all,

I'm still new to dax/power bi, but am creating a dashboard for our organization that charts applications over time. I'd like be able to compare how many applications we've received to date in the current program year compared to how many applications we've received last year. 

My understanding is that I'd need four key measures- a measure to calculate total applications, total applications LY (using sameperiodlastyear), cumulative applications using calculate([total applications], filter(all selected('calendar'), 'calendar'[date]<='calendar'[date]))), and then the same measure but to calculate LY cumulative applications. 

The issue I'm facing is that when I graph the measures I'm seeing misalignment between calendar years and the year our applications are attached to (program year). Since we hire for the following year and our program has a specific date range, an application that was submitted in August 2018 could be for the 2018-2019 program year that begins in September OR could be for the 2019-2020 beginning in September 2019. In the data set each application is one row and contains both the date it was submitted (as a date) and the program year for which they applied (as text).

In my measures, should I be filtering for using the program year? When I graph the measures and filter by calendar year it does not distinguish between program years so August would show more total applications (since there's no distinguishing between set up in my measure). 

Let me know if I can provide any additional context or clarity, and much appreciate the help! 

-Zach 

[v-cherch-msft]

Hi @Anonymous

You may refer to below post. Sample data and expected output will be helpful to provide an accurate solution.You can upload it to OneDrive or Dropbox and post the link here.

https://community.powerbi.com/t5/Desktop/DAX-Cumulative-for-a-filtered-year-and-previous-year/td-p/303781

How to Get Your Question Answered Quickly

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft,

Appreciate the reply! Here is a link to the data set on OneDrive - it's just a static copy of a report that I pulled into excel. Normally I would have a query that pulled the data straight from salesforce into Power BI. 

In terms of target output, I used a quick table I built in excel to show that by today, 10/31/2018, we had a total of 81 applications to the 2019-2020 program year, while we had 141 total applications by 10/31/2017 (same day last year) for the 2018-2019 program year. I made a super quick chart in excel also to show how I'd like to have this charted in Power BI:

I'll check out the other post that you linked, but would appreciate any insights you have in the meantime. 

Thank you again!

-Zach 

[v-cherch-msft]

Hi @Anonymous

It seems you may refer to below measure:

Measure =
CALCULATE (
    [Total Applications],
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Regards,

Cherie

[Ashish_Mathur]

Hi,

I am not sure of what you want.  Download my PBI file from here.