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
abristow
Frequent Visitor

Calculate fulfilled + open thru current month 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 1.png

 

Pic 2 (current issue): 

pic 2.png

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Should be along the lines of:

 

Measure =

  IF(

    MAX([Date]) <= EOMONTH(TODAY(),0),

    <do some calculation>,

    BLANK()

  )


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Should be along the lines of:

 

Measure =

  IF(

    MAX([Date]) <= EOMONTH(TODAY(),0),

    <do some calculation>,

    BLANK()

  )


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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?

 

 

pic 3.png

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()

  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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()

  )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors