Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KD13
Helper I
Helper I

Rolling Moving Sum issue with Blank Values

Hi All,

 

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 :

Last 6 months of sales =CALCULATE(SUM(Sales[Value]),DATEADD('00 -Calendar'[Date].[Date],-6,MONTH))

 

KD13_0-1684930917824.png

 

7 REPLIES 7
devanshi
Helper V
Helper V

pervious month sales =
VAR maxdate = MAX(Date tablename)

RETURN

CALCULATE(SUM(Sales),DATESINPERIOD(Date tablename,'Date'[date],-6,MONTH),'Date'[date]<=maxdate)

v-rzhou-msft
Community Support
Community Support

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
    )
)

My Sample:

vrzhoumsft_0-1685438497882.png

00 -Calendar = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",YEAR([Date])*100+MONTH([Date]),"MMM YY",FORMAT([Date],"MMM YY"))

Result is as below.

vrzhoumsft_1-1685438516851.png

Best Regards,
Rico Zhou

 

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)

 

KD13_0-1695140130826.png

 

Anonymous
Not applicable

@KD13 

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.

tamerj1
Super User
Super User

Hi @KD13 

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

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

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hello,

 

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 :

 

Last 6 months of sales =CALCULATE(SUM(Sales[Value]),DATEADD('00 -Calendar'[Date].[Date],-6,MONTH))

 

KD13_0-1684937208529.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors