Calculating daily numbers based on MTD numbers

[kkalyanrr]

Hello There,

I'm working on a dataset which has MTD values but my requirement is to retrieve the values daily which is opposite of Running Total.

Please help me on this.

When I select the date range, I need cummulative sum between these respective days.

Regards,

Kalyan..

[CheenuSing]

Hi @kkalyanrr

Can you share some data in the one drive or google drive and post the link here.  Also share the output expected from the dataset shared.

Cheers

CheenuSIng

[kkalyanrr]

@CheenuSing

Here is the Google Docs Link

https://docs.google.com/spreadsheets/d/11vAhF4v-Yznf61iNbwZoSCyHX2jbMUC-BLNsXCXZv7M/edit?usp=sharing

Thanks,

Kalyan..

[CheenuSing · Accepted solution]

Hi  @kkalyanrr,

Based on the data and your requirement, the approach to be taken is finding the previous days value and substracting it from the current days MTD value.

Try the following steps

1. I named the data table as Table2 

2. Created an Indexcolumn from 1 in the fact table.

3. Created an YearMonth column as

     YearMonth = Year(Table2[Date]) * 100 + Month(Table2[Date])

4.  Created a measure called DailySales

DailySales = 
var PreviousValue = 
 CALCULATE(
 SUM( Table2[Sales(MTD)] ),
 FILTER(  ALL(Table2) ,
 SUMX( FILTER( Table2, EARLIER(Table2[Index]) = Table2[Index] -1  && EARLIER([YearMonth]) = [YearMonth]  ),Table2[Sales(MTD)])
 )
 )
 
Return
Calculate(Sum(Table2[Sales(MTD)]) - PreviousValue)

In this measure we find the previousvalue first for each row and then finally substract ir from Sales[MTD]. This avoids creating a calculated column for the PreviousValue.

5.  Created measures DailyRevenue

     DailyRevenue = 
var PreviousValue = 
 CALCULATE(
 SUM( Table2[Revenue(MTD)] ),
 FILTER(  ALL(Table2) ,
 SUMX( FILTER( Table2, EARLIER(Table2[Index]) = Table2[Index] -1  && EARLIER([YearMonth]) = [YearMonth]  ),Table2[Revenue(MTD)])
 )
 )


Return
Calculate(Sum(Table2[Revenue(MTD)]) - PreviousValue)

6. Created measure DailyPageViews

DailyPageViews = 
var PreviousValue = 
 CALCULATE(
 SUM( Table2[PageViews(MTD)] ),
 FILTER(  ALL(Table2) ,
 SUMX( FILTER( Table2, EARLIER(Table2[Index]) = Table2[Index] -1  && EARLIER([YearMonth]) = [YearMonth]  ),Table2[PageViews(MTD)])
 )
 )
 
  Return
Calculate(Sum(Table2[PageViews(MTD)]) - PreviousValue)

Sample output

If this works for you please accept it as a solution also give KUDOS.

Cheers

CheenuSing

[kkalyanrr]

Thank you, Brilliant Stuff.

[v-yulgu-msft]

Hi @kkalyanrr,

Please try this measure to get the cummulative sum

Running total =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

Best regards,

Yuliana Gu

[kkalyanrr]

@v-yulgu-msft

Sorry I was looking for Daily Numbers based on MTD numbers..

In your example we have sales daily numbers, but I have Cummulative numbers in my dataset(running total).