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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kkalyanrr
Helper V
Helper V

Calculating daily numbers based on MTD numbers

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

Capture.PNGCapture1.PNG

1 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

Capture.GIF

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

Capture.GIF

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thank you, Brilliant Stuff.

 

 

v-yulgu-msft
Employee
Employee

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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