DAX - Items with no data

[potap]

Once again, I am stuck on something and ask for your help!

I got this measure :

=CALCULATE (
    SUM ( Sales[Sales] );
    Calender[isPast3Years] = TRUE ()
)

I have this pivot table that shows total sales by month :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]850[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Right now, the 2012 colomn doesn't appear because of the isPast3Years in the Calculate but I do have transactions in the Sales table. I don't have any transaction in May 2015 and in the following months, obviously.

If I drag this measure instead :

=IF ( ISBLANK ( [TotalSales] ); 0; [TotalSales] )

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]850[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]07[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

What I am looking for is a measure that gives 0 for every blank months in 2015 but not for 2012. I don't want to see any year that is not in the past 3. Tried Countrows() and other stuff but I wasn't able to figure something out

Thank you!!!

 

[potap]

Forgot to specify that there might be months in past years that have no transaction. In this example, let's say that August 2012 as 0 transaction so 0 row.

 

[theBardd]

You could add an extra CALCULATE test, filtering all of the items in Sales. I don't know what the row differentiator is, so I used a field called ID and DAX measure of

=IF(CALCULATE( [TotalSales], ALL( Sales[Id] ) ) > 0, IF( ISBLANK( [TotalSales] ), 0, [TotalSales] ) )

 

[potap]

Thanks but this is the same as the first measure because there is is no sale in May 2015 so TotalSales is not > 0 and I will get a blank, not a 0.

 

[potap]

Got it to work!!!

=
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( Sales );
            ALL ( Calender[Month] );
            Calender[isPast3Years] = TRUE ()
        )
    );
    BLANK ();
    IF (
        ISBLANK ( _Measure[Sales (3 years)] );
        0;
        _Measure[Sales (3 years)]
    )
)

 

[scottsen]

I am thinking this would work?

=CALCULATE (
0 + SUM ( Sales[Sales] );
Calender[isPast3Years] = TRUE ()
)

 

[potap]

I am thinking this would work?

=CALCULATE (
0 + SUM ( Sales[Sales] );
Calender[isPast3Years] = TRUE ()
)

Tried it. I do get 0 for every month in 2015 but 2012 is still there.

 

[scottsen]

Yep, my bad Now I am annoyed by your question, because it feels like it should be easy...

 

[scottsen]

= IF (COUNTROWS(FILTER(Calendar, Calendar[IsPast3Years])) > 0, 0, BLANK()) +
SUM(Sales[Sales])

Look at the current context for rows in the past 3 years... if there are any... add a 0 to your measure.

Should work and perform well, but still feels a bit weird to me. <shrug>. Back to my coffee...

 

[theBardd]

I think you want to cater for not showing 2012 even when there are values in those months because it is not in past 3 years so I would use

[COLOR=#333333][FONT=Consolas]=[/FONT][/COLOR][COLOR=#0070FF][FONT=Consolas]IF[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas] ([/FONT][/COLOR]
[COLOR=#0070FF][FONT=Consolas]COUNTROWS[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas] ([/FONT][/COLOR][COLOR=#0070FF][FONT=Consolas]FILTER[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas] ([/FONT][/COLOR][COLOR=#333333][FONT=Consolas] Calendar, Calendar[IsPast3Years] [/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas])[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas])[/FONT][/COLOR][COLOR=#333333][FONT=Consolas],[/FONT][/COLOR]
[COLOR=#0070FF][FONT=Consolas]IF[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas] ([/FONT][/COLOR][COLOR=#0070FF][FONT=Consolas]ISBLANK[/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas] ([/FONT][/COLOR][COLOR=#333333][FONT=Consolas] [TotalSales] [/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas])[/FONT][/COLOR][COLOR=#333333][FONT=Consolas], [/FONT][/COLOR][COLOR=#EE7F18][FONT=Consolas]0[/FONT][/COLOR][COLOR=#333333][FONT=Consolas], [TotalSales] [/FONT][/COLOR][COLOR=#D0D0D0][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#D0D0D0][FONT=Consolas])[/FONT][/COLOR]