topic Re: Calculate fulfilled + open thru current month only in DAX Commands and Tips

Calculate fulfilled + open thru current month only

abristow — Wed, 18 Mar 2020 19:46:42 GMT

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

Re: Calculate fulfilled + open thru current month only

Greg_Deckler — Wed, 18 Mar 2020 23:23:36 GMT

Should be along the lines of:

Measure =

IF(

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

<do some calculation>,

BLANK()

)

Re: Calculate fulfilled + open thru current month only

abristow — Thu, 19 Mar 2020 14:10:04 GMT

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?

Re: Calculate fulfilled + open thru current month only

Greg_Deckler — Thu, 19 Mar 2020 14:04:24 GMT

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

Re: Calculate fulfilled + open thru current month only

abristow — Tue, 24 Mar 2020 14:41:48 GMT

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!

Re: Calculate fulfilled + open thru current month only

Greg_Deckler — Tue, 24 Mar 2020 14:57:34 GMT

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

  )