Cumulative values with if isblank

DAX DAX Calculations
1

Hello you all
I’m trying to calculate a cumulative values but if there’s no data for that month and on, don’t show any data, you know? My measure is like this:
CALCULATE([B Transactions], FILTER( ALLSELECTED ( ‘Tab Dates’), ‘Tab Dates’[Date] <= MAX( ‘Tab Dates’[Date])))
and my matrix table is

Capture
Capture944×188 33.4 KB

I don’t have any data from May and so on of 2020.
I’d like to show a blank if there’s no data. I’ve tried with if (isblank and it didn’t work.
The year selected is 2020 and this is the only filter in this report
Can anyone help me?
Thank you all
Pedro

2

@pedroccamara What did you check with IF ( ISBLANK() ?

3

I did :

B Transactions Cum =
IF(
    ISBLANK(
        CALCULATE(
            [B Transactions],
            FILTER(
                ALLSELECTED( 'Tab Dates' ),
              'Tab Dates'[Date] <= MAX( 'Tab Dates'[Date] )
            )
        )
    ),
    BLANK(),
    CALCULATE(
        [B Transactions],
        FILTER(
            ALLSELECTED( 'Tab Dates' ),
          'Tab Dates'[Date] <= MAX( 'Tab Dates'[Date] )
        )
    )
)

I think i saw a video with this but i’m not sure and it didnt work.
Thanks @AntrikshSharma

4

@pedroccamara Running total won’t return BLANK () so there is not point in checking that:

if you want running total to stop after a certain month of every year then use something like this:

Running Total EveryYear BLANK =
IF (
    NOT SELECTEDVALUE ( Dates[Month Number] ) > 6,
    IF (
        ISINSCOPE ( Dates[Date] ) || ISINSCOPE ( Dates[Month] ),
        CALCULATE ( [Total Sales], DATESYTD ( Dates[Date] ) )
    )
)

If you want blank only for a specific year then:

Running Total CertainYear BLANK =
IF (
    NOT (
        SELECTEDVALUE ( Dates[Month Number] ) > 6
            && SELECTEDVALUE ( Dates[Calendar Year Number] ) = 2008
    ),
    IF (
        ISINSCOPE ( Dates[Date] ) || ISINSCOPE ( Dates[Month] ),
        CALCULATE ( [Total Sales], DATESYTD ( Dates[Date] ) )
    )
)

image
image796×818 42.9 KB

4 Likes
5

Hey @AntrikshSharma
Awesome solution!!! Can i ask you one last question about your solution? What if you could dynamically change the 5 in your dax? I mean, i have a monthofset in my date table but it shows last month, september. It should show in this case, may. Is this possible? i mean to show the last month of that table of sales?
Thanks a lot
PEdro

6

@pedroccamara You could try something like this:

=
IF (
    VAR MaxDate =
        CALCULATE ( MAX ( Sales[Date] ), ALL ( Sales ) )
    VAR MaxMonth =
        MONTH ( MaxDate )
    VAR MaxYear =
        YEAR ( MaxDate )
    VAR Result =
        NOT (
            SELECTEDVALUE ( Dates[Month Number] ) > MaxMonth
                && SELECTEDVALUE ( Dates[Calendar Year Number] ) = MaxYear
        )
    RETURN
        Result,
    IF (
        ISINSCOPE ( Dates[Date] ) || ISINSCOPE ( Dates[Month] ),
        CALCULATE ( [Total Sales], DATESYTD ( Dates[Date] ) )
    )
)
3 Likes
7

Hey @AntrikshSharma
Such a great solution !!
Thanks a lot!!!

1 Like
8

I’m so sorry @AntrikshSharma but i thought i could do it myself…:
As you know, my visual has months by accounts till the Max Date of sales. What if i have a visual without any months but still the year selected on a filter? Just the total of each accounts, but till that Max Date month.
Believe me, i tried to delete some parts of the measure above and didn’t work out. I need that because at the end i will have the same measure but for last year and the difference.
Can you help me please? I also need to understand it, you know?

9

@pedroccamara Now we would need some data because it is difficult to visualize.

10

I’m not with my computer now, but can you imagine a measure with dateadd -1 year, till that max date measure? I believe this would be what I need…or your measure but for sales ly (it won’t work if you replace the measure with sales ly…
The idea is to have, instead of months in the visual you will have Year and year -1

11

Hey @AntrikshSharma, problem solved and thanks to your measure.
I already have a measure here…which gives me the last date for this table

MaxDate (Tab Bal Transactions) =
CALCULATE ( MAX ( ‘Tab Balance Transactions’[T Date]), ALL ( ‘Tab Balance Transactions’ ))

then i got another measure to give me the month of that last date measure

MaxMonth (Tab Bal Transactions) = MONTH(
CALCULATE ( MAX ( ‘Tab Balance Transactions’[T Date]), ALL ( ‘Tab Balance Transactions’ )) )

Then i’ve made a measure of this year sales, but only until the last month

B Transactions Acum by Month =
VAR MaxMonth = [MaxMonth (Tab Bal Transactions)]

RETURN

CALCULATE( [B Transactions], ‘Tab Dates’[MonthOfYear] <= MaxMonth)

Now, it’s easy to have another measure for LY by just changing the calculate measure to [B Transactions LY]

And that’s it.
Thanks a lot for all your patience of helping me.
Best regards
Pedro

1 Like
12

Great, solving something yourself is the next steps towards learning!

13

Super support @AntrikshSharma

Amazing value you’re providing. Well done

Sam

2 Likes