cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nsadams87xx Member
Member

Year to date last year vs this year totals by month

Hi everyone,

 

I'm trying to use a graph to make a year to date comparison to this year and year to date comparison to last year's medical patient volume as show here:

 

1.JPG 

 

 

2.JPG

 

 

 

The trouble is that when I try to calculate "total pt vol Billing" by sameperiodlastyear() (to capture last year's data) and graph the Total Pt Vol Billing along side it, I get this

6.JPG

 

3.JPG

 

 

I'm guessing that because Total Pt Vol Billing is taking into account my entire date dimension (which starts Jan 1,2017 and goes into Sept 2018).  

 

If I try to calculate this year's volume and graph it with my sameperiodlastyear measure, I get this:

4.JPG5.JPG

I want the graph in my first screenshot to just stop at my data's completed month regardless of the year (right now it's September).  Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Year to date last year vs this year totals by month

Hi @nsadams87xx,

 

I guess the [month_name] likes January, February while the month(today()) is number. If so, please add a new column of numeric months for comparison. 

You can click "See details" to see something like the snapshot.

Year-to-date-last-year-vs-this-year-totals-by-month

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
v-jiascu-msft Super Contributor
Super Contributor

Re: Year to date last year vs this year totals by month

Hi @nsadams87xx,

 

What's the formula of [Total Pt Vol Billing]? Can you share a dummy sample? 

 

New Measure =
IF (
    MIN ( DimDate[month_name] ) <= MONTH ( TODAY () ),
    [Total Pt Vol Billing],
    BLANK ()
)

 

Best Regards,
Dale

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

Re: Year to date last year vs this year totals by month

The formula is just SUM(*column*).  That column is just a counting column basically.  Every row has a 1 in it which is a way to say that was a medical encounter at one of our facilities.

nsadams87xx Member
Member

Re: Year to date last year vs this year totals by month

Hi Dale,

 

Thanks for the suggested measure.  I actually did try that but I ran into an issue where Power BI thinks the min() month is April and it broke the visualization.  My month_name column is sorted by my month_id column in my date dimension too so I found this behavor odd.

v-jiascu-msft Super Contributor
Super Contributor

Re: Year to date last year vs this year totals by month

Hi @nsadams87xx,

 

Can you share a dummy sample, please?

 

Best Regards,
Dale

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

Re: Year to date last year vs this year totals by month

Hi Dale,

 

Here's a screenshot.  The data I'm summing up is simply just 1's for every single row.

 

Sample.JPG

 

Dummy sample =
IF (
MIN ( date_dim[month_name] ) <= MONTH ( TODAY () ),
[Pt Volume - BIlling],
BLANK ()
)

 

"Dummy sample" measurement ended up doing this:

 

sample 2.JPGsample 3.JPG

 

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Year to date last year vs this year totals by month

Hi @nsadams87xx,

 

I guess the [month_name] likes January, February while the month(today()) is number. If so, please add a new column of numeric months for comparison. 

You can click "See details" to see something like the snapshot.

Year-to-date-last-year-vs-this-year-totals-by-month

 

Best Regards,
Dale

Community Support Team _ Dale
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: 52 members 928 guests
Please welcome our newest community members: