Forum Discussion

m_wex's avatar
m_wex
Helper I
5 years ago

Problem with Cumulative Sales

Hello everyone.

 

Running into troubles with cumulative sales. Have created a measure that shows ranked $ sales by part number (Top 5, 10, 25, 50, all). The result correctly shows the sales for the time period selected. The DAX below is the formula which controls the ranked detail and this works fine.

 

Material-Description Sales Ranking =

VAR

    RankingDimesion=Values(Sales[Material])

Var

    RankingSelect = [Ranking Select PN]

Return

CALCULATE([Total Sales],

    Filter( RankingDimesion,

        RankX(all(Sales[Material], Sales[Description]) ,[Total Sales],  ,desc )<= RankingSelect))

 

To accompany the detail, a graph has been created showing cumulative sales for the ranking selected. This is done through the following DAX:

This part also works correctly.

 

Material YTD Overview =

VAR lastsaledate = maxx(all(Sales[Calendar day]), Sales[Calendar day])

VAR ytdsales = TOTAlYTD([Material-Description Sales Ranking],

Dates[Date])

Return

if(min(Dates[Date])<=lastsaledate,ytdsales,blank() )

 

Along with YTD the graph includes previous year-to-date for the ranked part numbers. This is where problem has started. I have tried several approaches using sameperiodlastyear, etc. and have been unable to get the PYTD information to be correctly displayed.

 

Created a DAX to show the start/end date of the YTD sales information so that DATESBETWEEN can be used. But can't get this to work either. Working on a sample PBIX but in the meantime does anyone have any thoughts?

 

Thanks,

M_WEX

Cumulative Totals

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    m_wex , for last year try like

     

    Material YTD Overview =

    VAR lastsaledate = maxx(all(Sales[Calendar day]), Sales[Calendar day])

    VAR ytdsales = TOTAlYTD([Material-Description Sales Ranking],

    dateadd(Dates[Date],-1, year))

    Return

    if(min(Dates[Date])<=lastsaledate,ytdsales,blank() )

    • m_wex's avatar
      m_wex
      Helper I
      5 years ago

      Hi amitchandak, thank you for the feedback on the PYTD. Really appreciate it. I tried the solution and unfortunately the result did not turn out correct. The formula works but the result was the same as previous attempts. The issue appears to be centered around the top x part numbers. For PYTD, it looks like the DAX is dropping the of the top x and replacing it with a topx of the previous period. The trick, which I can't figure out, is that the part numbers for topx PYTD have to be the same part numbers for YTD which is the primary measure of topx.

       

      I was able to get total YTD, PYTD by part number working using the following Frankenstein combination of DAX. The lines in red are for the PYTD and blue is YTD. The DAX for YTD was done differently than originally posted as I was trying different approaches to solve the problem of cumulative topx PYTD,

       

      Formula1 Date1 = STARTOFMONTH(Sales[Calendar day])

      Formula2: Date2 = edate([Date 1],-12)

      Formula3: Date 3 = ENDOFMONTH(Sales[Calendar day])

       

      Formula4: Date 4 = edate([date 4],-12)

       

      Formula5: date between = calculate (sum(Sales[invoiced turnover]), DATESBETWEEN(Dates[Date],[Date2], [Date 4]))

       

      Formula6: Ranked Sales =  if(not(isblank([Material-Description Sales Ranking])),CALCULATE([total sales]))