How to filter out rows from an output table whose values are zero for a period of time?

[Anonymous]

I have the output table below where I have written a measure for each volume for 3,2,1 and "this" weeks.

How can I filter out rows from it, using a slicer, for when the volume during a certain period of time is zero?

For example:

- If the Volume in the last 3 weeks from today is zero only the red rows will disappear from the output table

- If the Volume in the last 2 weeks from today is zero only the green and red rows will disappear from the output table

The measures that I have for the Volumes are 

Volume this week =

var DateToday = TODAY()

var Date1weekAgo = DateToday - 7

var VolumeThisWeek = CALCULATE(SUM(Messages[Number Of Messages]),FILTER('Date','Date'[Date] <= DateToday && 'Date'[Date] >= Date1weekAgo))

Return

if(VolumeThisWeek = BLANK(), 0,

if(VolumeThisWeek > 0,VolumeThisWeek))

Volume 1 week ago =

var DateToday = TODAY()

var Date2weeksAgo = DateToday - 14

var NumOfMessage2WeeksAgo = CALCULATE(SUM(Messages[Number Of Messages]),FILTER('Date','Date'[Date] <= DateToday && 'Date'[Date] >= Date2weeksAgo))

var NumOfMessageThisWeek = [Volume this week]

var Volume1WeekAgo = NumOfMessage2WeeksAgo - NumOfMessageThisWeek

Return

if(Volume1WeekAgo = BLANK(), 0,

if(Volume1WeekAgo > 0, Volume1WeekAgo)

)

Volume 2 weeks ago =

var DateToday = TODAY()

var Date3weeksAgo = DateToday - 21

var NumOfMessage3WeeksAgo = CALCULATE(SUM(Messages[Number Of Messages]),FILTER('Date','Date'[Date] <= DateToday && 'Date'[Date] >= Date3weeksAgo))

var NumOfMessages2WeeksAgo = [Volume this week]+[Volume 1 week ago]

var Volume2weeksAgo= NumOfMessage3WeeksAgo - NumOfMessages2WeeksAgo

Return

if(Volume2weeksAgo = BLANK(), 0,

if(Volume2weeksAgo > 0, Volume2weeksAgo)

)

@MFelix 

[MFelix]

Hi @Anonymous ,

To what I can understand you want to select the values on the slicer and hide all rows that have all the values below that week 0, so when you are selecting 0 in the last 3 weeks means that there are no sales in week 1, 2, 3 correct?

First of all believe that your measure for the last 1 , 2, 3 and so on need to be change to cumulative values since you are making the value of this week plus the ones on the previous week.

So if the measures are created using a DATEBETWEEN is easier and without any issues. But again just looking this from aside and without any data. 

Also the used of nested IF complicates your model in this case for example for the current week you can change by a COALESCE try the following measures (just change the last syntax in each one:

Volume this week =
VAR DateToday =
    TODAY ()
VAR Date1weekAgo = DateToday - 7
VAR VolumeThisWeek =
    CALCULATE (
        SUM ( Messages[Number Of Messages] ),
        FILTER ( 'Date', 'Date'[Date] <= DateToday && 'Date'[Date] >= Date1weekAgo )
    )
RETURN
    COALESCE ( VolumeThisWeek, 0 )



Volume 1 week ago =
VAR DateToday =
    TODAY ()
VAR Date2weeksAgo = DateToday - 14
VAR NumOfMessage2WeeksAgo =
    CALCULATE (
        SUM ( Messages[Number Of Messages] ),
        FILTER ( 'Date', 'Date'[Date] <= DateToday && 'Date'[Date] >= Date2weeksAgo )
    )
VAR NumOfMessageThisWeek = [Volume this week]
VAR Volume1WeekAgo = NumOfMessage2WeeksAgo - NumOfMessageThisWeek
RETURN
    COALESCE ( Volume1WeekAgo, 0 )



Volume 2 weeks ago =
VAR DateToday =
    TODAY ()
VAR Date3weeksAgo = DateToday - 21
VAR NumOfMessage3WeeksAgo =
    CALCULATE (
        SUM ( Messages[Number Of Messages] ),
        FILTER ( 'Date', 'Date'[Date] <= DateToday && 'Date'[Date] >= Date3weeksAgo )
    )
VAR NumOfMessages2WeeksAgo = [Volume this week] + [Volume 1 week ago]
VAR Volume2weeksAgo = NumOfMessage3WeeksAgo - NumOfMessages2WeeksAgo
RETURN
    COALESCE ( Volume2weeksAgo, 0 )

In order to give you better answer can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous · Accepted solution]

Something like below would work. I have created a dummy dimension for days equalling the week you would like to filter on.

Example volume 3 weeks final measure:

3weeksvolumefinal =
var var3week = [volume3weeks]
Var var2week = [volume2weeks]
Var var1week = [volume1week]
Return
Switch(True(),

Selectedvalue(days)="1 week", if(var1week = 0, blank(), var3week),

Selectedvalue(days) = "2week", if(var2week = 0, blank(), var3week), Selectedvalue(days) = "3week", if(var3week =0, blank(), var3week),

var3week)

Then just copy the same approach for the other measures which make new up the columns in your output.