Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nsadams87xx
Helper III
Helper III

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

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.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

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.

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.

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.

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

 

 

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.