cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zmetzinger Frequent Visitor
Frequent Visitor

Cumulative Applications over LY Cumulative Applications

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 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Cumulative Applications over LY Cumulative Applications

Hi @zmetzinger

 

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/3...

 

How to Get Your Question Answered Quickly

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zmetzinger Frequent Visitor
Frequent Visitor

Re: Cumulative Applications over LY Cumulative Applications

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:

YOY Sample Chart.png

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

 

Thank you again!

 

-Zach 

 

 

 

Super User
Super User

Re: Cumulative Applications over LY Cumulative Applications

Hi,

 

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

Community Support Team
Community Support Team

Re: Cumulative Applications over LY Cumulative Applications

Hi @zmetzinger

 

It seems you may refer to below measure:

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

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.