Measure to calculate zero values

[Joerg]

Dear All

I have 2 tables, a table with periods and the other with values and dates.

The relation between both are columns "YYYY-MM" and "YearMonth".

When I put in a matrix column "YYYY-MM" and "Value" I get the sums for each month.

But I am missing the column for "2021-07", I have expected to see a zero because for that month no Values are existing.

How can I achive this with a measure ?

And a second question.

I have a slicer for the "YYYY-MM" column. Additionally a table MonthBack with values of 3; 6; 9 and 12.

Is it possible to select with the slicer a month and the matrix only shows values for this selected month and the number of choosen MonthBack ?

E.g. when I select 2021-06 and have the value MonthBack of 6, the matrix shows me the values from 2021-01 to 2021-06 ?

I would like to attach a pbix file for your reference, but there is no possibility for me to upload it.

Thanks for your help.

[Anonymous]

@Joerg 

For the cumulative rolling total to repeat over the months with 0

Try this:

Cumulative Sales =

IF(MIN(Period[YYYY-MM])<=CALCULATE(MAX(Yearmonth[YYYY-MM]),
   ALL(Yearmonth)),CALCULATE([Sum of value],
FILTER(All(Period[YYYY-MM]),Period[YYYY-MM]<=MAX((Period[YYYY-MM])))),
   BLANK())

[Joerg]

@ Rena

@ Niiru1

Thanks to you both, your proposals are good input for me. The only thing is my requirement for a selection of the period to be displayed.

Let me explain.

I need a measure showing me for each period the value, if blank it should show zero. This is as per Rena's solution fine.

Also I need the same but with accumulated values, this works fine with the solution from Niiru1.

What I now need is an independent slicer where I can select a period, and the table shows me the values for that period and the following 5 months. The aggregation should stay as it is, only the view should be filtered.

How can I achieve that ? I tried myself to adapt this, but my knowledge is too limited.

Thanks for your help.

[Joerg]

@ Niiru1

Thanks for this approach, it works if I do not care about the period.

But my requirement is, that I can control via an independent slicer which range of period I do display in the matrix. 

[Anonymous]

Hi @Joerg ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below

Measure = SUM('Table'[Value])+0

2. Create a matrix visual (Columns: YYYY-MM (from the table with period)), Values: Measure)

In addition, you can refer the solution in the following threads to get it. The solution is to create or have a dimension table first, then create the measure and process the blank values. There are three main approaches.

Display zero instead of blank in matrix

Measure = SUM('Table'[Colname])+0 
Measure = IF ( ISBLANK ( SUM ( 'Table'[Colname] ) ), 0, SUM ( 'Table'[Colname] ) )
Measure = COALESCE ( SUM ( 'Table'[Colname] ), 0 )

Best Regards

[Joerg]

Thanks, this works.

Maybe you have a solution for my second question as well ?

[Anonymous · Accepted solution]

Hi @Joerg ,

I updated my sample pbix file(see attachment), please check whether that is what you want.

1. Create another yearmonth dimension table(Don't create any relationship with another tables) and apply the yearmonth field as slicer

Yearmonth = VALUES('Period'[YYYY-MM])

2. Create two measures as below to get the sum of values

TempsValue = 
VAR _selym =
    SELECTEDVALUE ( 'Yearmonth'[YYYY-MM] )
VAR _selyyyymm =
    SELECTEDVALUE ( 'Period'[YYYY-MM] )
VAR _ymvalue =
    VALUE ( CONCATENATE ( LEFT ( _selym, 4 ), RIGHT ( _selym, 2 ) ) )
VAR _svalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[YearMonth] = SELECTEDVALUE ( 'Period'[YYYY-MM] )
                && VALUE (
                    CONCATENATE ( LEFT ( 'Table'[YearMonth], 4 ), RIGHT ( 'Table'[YearMonth], 2 ) )
                ) <= _ymvalue
        )
    )
RETURN
    IF ( _selyyyymm <= _selym, _svalue + 0, BLANK () )

Sum of value = SUMX(VALUES('Period'[YYYY-MM]),[TempsValue])

3. Create matrix visual (Columns: YYYY-MM(from the table with period info)  Values:[Sum of value])

Best Regards

[Joerg]

Hello

Would it be possible to show the values as a running total ?

Like the second row here ?

[Joerg]

Hello,

that is awesome, thanks a lot 😀

[amitchandak]

@Joerg , You can add +0 to the measure. It might give all months.

try a code like this

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

[Joerg]

Thanks for your reply.

Unfortunately it still misses the month where no values exist