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
Fred010
New Member

Break down cumulative

Hi, 

I'm trying to break down a table with daily cumulatives to totals per day, something like this:

[cumulative of today] - [cumulative of yesterday] = [total of today]

 

I'm using this json as source: https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json

 

I'm rather new to Power BI Desktop and couldn't find the right resources to answer my question.

So some help would be appreciated.

4 REPLIES 4
v-xicai
Community Support
Community Support

Hi  @Fred010  ,

 

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

v-xicai
Community Support
Community Support

Hi @Fred010 ,

 

You may create measure like DAX below.

 

total of today=CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Date] =TODAY()))

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

ryan_mayu
Super User
Super User

@Fred010 

What's the expected output?

If you want to get total today, why not just calculate the total today directly?

totaltoday = CALCULATE(sum('COVID-19_aantallen_gemeente_cumulatief'[Total_reported]),FILTER('COVID-19_aantallen_gemeente_cumulatief',DATEVALUE('COVID-19_aantallen_gemeente_cumulatief'[Date_of_report])=today()))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@Fred010 , with date table.

 

Way to get last day, continuous or noncontinuous - Measure

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf])  ),Table[Date]) ,Table[Date],Day)

Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

 


This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

or

sum('order'[Qty])

 

Take a diff of the two

 

Column way

Last Sales Date = maxx(FILTER(Sales1,Sales1[Sales Date] =EARLIER(Sales1[Sales Date])-1),Sales1[Sales Date])
Last Non Cont Sales Date = var _max = maxx(FILTER(Sales1,Sales1[Sales Date] < EARLIER(Sales1[Sales Date])),Sales1[Sales Date])
return maxx(FILTER(Sales1,Sales1[Sales Date] =_max),Sales1[Qty])

 

Refer this

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

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.