Forum Discussion

Joerg's avatar
Joerg
Icon for Helper I rankHelper I
5 years ago
Solved

Measure to calculate zero values

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 "...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Joerg
Icon for Helper I rankHelper I
5 years ago

@ 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.