need to have the total of the running total

[Lejouquard]

Hi Guys,

I'm trying to calculate the sum of a a running stock variation in yello below :

I created a measure to compare the cumulative stock with M-1 (with PARALLELPRIOD), it works to have the variation amount for each month but my total variation = 0 .

Any idea to calculate the variation with a right total?

thx

[FrankAT · Accepted solution]

Hi @Lejouquard ,

I used your sample data and get the following approach:

Sum of Stock variation = SUM('Table'[Stock variation])

Running Total = 
VAR _RunningTotal = 
    CALCULATE(
        [Sum of Stock variation],
        FILTER(
            ALL('Table'),
            'Table'[Monthnumber] <= MIN('Table'[Monthnumber])
        )
    )
RETURN
   IF(
       NOT(HASONEVALUE('Table'[Stock variation])),
       [Sum of Stock variation] ,
        _RunningTotal
    )

 With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[v-robertq-msft · Accepted solution]

Hi, @Lejouquard 

According to your description, you want to display a correct total value for the measure [Stock variation] which compare the cumulative stock with M-1, I‘ve also created some data to create a measure to achieve the output, you can take a look:

This is my test data:

Then I create a measure [Stock variation] and place it into the table chart:

Stock variation =

var _lastvalue=

CALCULATE(SUM('Table'[Cumulative stock]),FILTER(ALL('Table'),[Month number]=MAX([Month number])-1))

return

MAX('Table'[Cumulative stock])-_lastvalue

As we can see, the total of this measure is incorrect.

Then I create another measure:

Stock variation1 =

var _new=

SUMMARIZE('Table',[Month],"1",[Stock variation])

return

IF(HASONEVALUE('Table'[Month]),[Stock variation],SUMX(_new,[1]))

And I place this measure into the table chart to replace the original measure [Cumulative stock], then I can get the correct total, like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post your sample pbix file(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lejouquard]

Many thanks @v-robertq-msft 

[FrankAT · Accepted solution]

Hi @Lejouquard ,

I used your sample data and get the following approach:

Sum of Stock variation = SUM('Table'[Stock variation])

Running Total = 
VAR _RunningTotal = 
    CALCULATE(
        [Sum of Stock variation],
        FILTER(
            ALL('Table'),
            'Table'[Monthnumber] <= MIN('Table'[Monthnumber])
        )
    )
RETURN
   IF(
       NOT(HASONEVALUE('Table'[Stock variation])),
       [Sum of Stock variation] ,
        _RunningTotal
    )

 With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Lejouquard]

Many Thanks @FrankAT !

[amitchandak]

@Lejouquard , Not very clear.

We can get cumm with date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Sales till last month  = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=eomonth(max(date[Date]),-1)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Lejouquard]

Hi @amitchandak thank you for your reply

My issue is about the sum of the variation.

In your example if I create the following measure to get the monthly variation : [Cumm Sales] - [Cumm Sales til last month]

My total will be wrong.

[amitchandak]

@Lejouquard , total should be diff of lat month.

But you can explore like

Sumx(values(Table[month]),[Cumm Sales] - [Cumm Sales til last month])