Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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:
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?
Solved! Go to 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.
Best Regards,
Dale
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
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
Hi Dale,
Here's a screenshot. The data I'm summing up is simply just 1's for every single row.
Dummy sample =
IF (
MIN ( date_dim[month_name] ) <= MONTH ( TODAY () ),
[Pt Volume - BIlling],
BLANK ()
)
"Dummy sample" measurement ended up doing this:
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.
Best Regards,
Dale
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |