cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help doing some calculations on the data

Good Day.

 

I am pretty new to PowerBI/DAX. Need expert advise to help me achive below objectives.

 

1. Rolling/cumulative sum for the facts by day, province/state, country/region

2. How to calculate daily growth (change %) for various facts by day, province/state, country/region

3. Can we do what if analysis on this data? If yes provide some examples

 

The PBIX file can be downloaded from below:

 

https://1drv.ms/u/s!AmVyBpEjWWRca2Kdxy3UMNWO4gY

 

NOTE: the Keep flag is used to keep only valida records and all calculation should be done for therecords flagged as keep

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Hello

You can translate monthly and/or all in a single graph depends on the communication and analysis intension of your previous audience.

to solve the first question uses a quick mean according to what will be counted in this case choose PROVINCE / STATE add it to a line chart and to perform the quick measurement, right click, the last option

Nueva medida rapidaNew quick measure

loads a screen and we will search in totals, cumulative total elegir total acumulado choose total accumulated Totalacumulado Total accumulated

We agree and the measure is lowered/moved/moved/changed as SECONDARY VALUES, as such you will have the graph of the journal and the accumulated one with an axis of DAYS.

el acumulado lo pones como 2do valorthe accumulated you put it as 2nd value

for a better visualization a segmentation of data with the date/month.

Ss.

View solution in original post

2 REPLIES 2
Highlighted
Helper I
Helper I

Hello

You can translate monthly and/or all in a single graph depends on the communication and analysis intension of your previous audience.

to solve the first question uses a quick mean according to what will be counted in this case choose PROVINCE / STATE add it to a line chart and to perform the quick measurement, right click, the last option

Nueva medida rapidaNew quick measure

loads a screen and we will search in totals, cumulative total elegir total acumulado choose total accumulated Totalacumulado Total accumulated

We agree and the measure is lowered/moved/moved/changed as SECONDARY VALUES, as such you will have the graph of the journal and the accumulated one with an axis of DAYS.

el acumulado lo pones como 2do valorthe accumulated you put it as 2nd value

for a better visualization a segmentation of data with the date/month.

Ss.

View solution in original post

Highlighted
Super User IV
Super User IV

@Chin , All with a date table

 

examples

 

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)

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

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))

 

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))

Rolling 3 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))

 

What if is a bit topic , what you need in that -https://docs.microsoft.com/en-us/power-bi/desktop-what-if

 

 

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors