cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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
v-cherch-msft Super Contributor
Super Contributor

Re: Cumulative Applications over LY Cumulative Applications

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

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.
Anonymous
Not applicable

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.

v-cherch-msft Super Contributor
Super Contributor

Re: Cumulative Applications over LY Cumulative Applications

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

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 1,021 guests
Please welcome our newest community members: