i'm trying to calculate the cumulative sales of last 6 months by product but i'm facing a challenge with the months with no data.
for example :
in the below table, eventhpugh the 1st product has no sales in March 23 , March 23 value should reflect : March + feb+jan+dec+nov+oct) but instead of this it's showing blank.
this is the formula that i'm using and i can't find a way to force the calculation even when some of the months are blank :
Hi @KD13 ,
I suggest you to try code as below to create a measure.
Last 6 months of sales = CALCULATE ( SUM ( Sales[Value] ), FILTER ( ALL ( '00 -Calendar' ), '00 -Calendar'[Date] <= MAX ( '00 -Calendar'[Date] ) && '00 -Calendar'[Date] >= EOMONTH ( MAX ( '00 -Calendar'[Date] ), -6 ) + 1 ) )
00 -Calendar = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",YEAR([Date])*100+MONTH([Date]),"MMM YY",FORMAT([Date],"MMM YY"))
Result is as below.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
THanks for your response.
the only issue with your solution is that eventhough the last month of sales is March 2023 the last 6 monts of sales calculation goes until August 2023 while it mus end at last real month of sales (March 2023)
I have recently solved a similar issue
Mine was based on years instead.
Solution I used:
1. Created a new table containing all years
2. Connected this table to the main one with a join on the year
3. Created a Running total from PBI quick measures using the Years column in the new table (so Base Value= [Value you want to show cumulative/running total] & Field = [Years from new column])
4. Make sure to place your Years/Months column from the NEW table on the chart or table you want to show
worked like a charm!
Edit: PowerBI will show blank if there are no sales/profit/value in a certain time period, which is very frustrating thinking that logically, it should carry the previous value as you stated in your post.
I don't use time intelligence functions in my code. They don't seem intelligent to me but seems that this not the case for you. Going along with that I guess DATEADD is not the correct function to use in this case rather you can use DATESINPERIOD like
SUM ( Sales[Value] ),
DATESINPERIOD ( '00 -Calendar'[Date], MAX ( '00 -Calendar'[Date] ), -6, MONTH )
@KD13 Hard to say exactly what is going on with the information provided but this may help, you would just use SUMX instead of AVERAGEX.
thanks for your response but the issue is not solved with the proposed method.
the main challenge is that the Cumulative calculation stops wheneve ther's blank value.
Please see the below example :
in the row Sales by month we have the monthly sales.
the the cumulative sales row (in Green) , this is how the Moving 3 months sales should be working.
in the red row, this is how the current dax is behaving. whenever there's a blank the calculation stops.
the dax i'm using is as follows :
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.