Forum Discussion
Helper IMeasure 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 "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.
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
10 Replies
- amitchandak
Super User
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))
JoergThanks for your reply.
Unfortunately it still misses the month where no values exist
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])+02. 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 ?
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
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
@ 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.
- 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.
