Cumulated Sales Metrics failure with Blank Values

[KD13]

Hi All,

I've been struggling with creating the right metric for the last 6 months cumulative sales and i hope that you can help me with it.

In summary, i'm facing issues with blank values and my metric is not considering the period where the data is null.

Please see the below example :

In the below table you can see my original dataset, it reflects the sales by product  from Dec-21 to May-22 (6 Months).

As you can see, Product 4 doest not have data from Feb 22 to May 22 .

Same for Product 9, May 22 is blank

When i try to create the metric to calculate the last 6 months of sales per period i face the below issue.

i'm able to have the calculation only for the product without blank values.

as you can see from the below calculated table, i'm calculating the last 6 months of sales for May 22

==> Product 9 and Product 4 are not taken into consideration by my metric as May 22 the starting month is blank.

in the other side, Product 3 is considered eventhough it has some blank datapoints (Not May 22)

This is my metric formula :

Sales MST =

var _lastdate =

CALCULATE(MAX('sales_table'[date]),ALL(sales_table[Current Local Product]))

return

  CALCULATE(SUM(sales_table[Sales]),

  FILTER(

    ALL('00 -Calendar'),

    '00 -Calendar'[Date]<= _lastdate

  && '00  -Calendar'[Date] >= EOMONTH ( MAX ( '00 -Calendar'[Date] ), -6 )))

I hope that you can help me to fix it.

[amitchandak · Accepted solution]

@KD13 , with help from the Calendar table, joined to date of your table

Try a measure like this. In the visual use field from Calendar for month year

Rolling 6 = CALCULATE(SUM(sales_table[Sales]),,DATESINPERIOD('00 -Calendar'[Date],MAX('00 -Calendar'[Date]),-6,MONTH))

Rolling 6 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('00 -Calendar'[Month Year],'00 -Calendar'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[KD13]

Hello,

thanks for your reply, bu thhis suggestion does not solve the issue.

When the starting month of the 6 months period is blank, the calculation stops and does not take into consideration the reamining months.