Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have an Orders table with [Qty Shipped] and [Qty Open].
I want to provide a line graph and matrix that only show values thru the current month based on today (pic 1). These values should include any open orders for the current month. I want this to happen dynamically instead of having to use a date slicer.
However, I cannot figure out how to get next month to be excluded from a calculation (pic 2).
I'm working in a Direct Query model, so I can only write measures --- Do I need to have a calculated column created?
Thanks in advance for any help!
Pic 1 (goal):
Pic 2 (current issue):
Solved! Go to Solution.
Should be along the lines of:
Measure =
IF(
MAX([Date]) <= EOMONTH(TODAY(),0),
<do some calculation>,
BLANK()
)
Should be along the lines of:
Measure =
IF(
MAX([Date]) <= EOMONTH(TODAY(),0),
<do some calculation>,
BLANK()
)
@Greg_Deckler thank you! That did the trick. One follow-up question...is there any way to make the YTD total show up on the right side of the table?
Ah yes, the total line. It's measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376. Also, this Quick Measure, Measure Totals, The Final Word might get you what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
But, in your case I think we could do something simple like this:
Measure =
VAR __Total = COUNTROWS('Table') = COUNTROWS(ALL('Table'))
RETURN
IF(
MAX([Date]) <= EOMONTH(TODAY(),0) || __Total,
<do some calculation>,
BLANK()
)
@Greg_Deckler just getting around to trying this. I'm green enough in DAX that I don't totally comprehend how it worked...but it did and I greatly appreciate your help!
Great @abristow - here is an explanation. Code below for ease of reference.
__Table - What this is doing is counting the rows within the current context and comparing it to the count of rows with all context removed. In your case, these are only equal (return of TRUE) in the Grand Total row so probably superfluous to your situation.
The other check gets the MAX of the date within the current context and compares it to the end of the current month's date. So, for you row total, the max of the date within the current context is going to be some date in March and so this forces the calculation to occur.
Now, specifically why it actually works in your case, well, I technically have no idea why it wasn't working because I have no idea what your measure is! But apparently it was some kind of calculation that was returning blank in the 2020 row total row and this apparently fixed that issue. 🙂
Measure =
VAR __Total = COUNTROWS('Table') = COUNTROWS(ALL('Table'))
RETURN
IF(
MAX([Date]) <= EOMONTH(TODAY(),0) || __Total,
<do some calculation>,
BLANK()
)
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |