Help doing some calculations on the data

[Anonymous]

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

[amitchandak]

@Anonymous , 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-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[Don_Alexis · Accepted solution]

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

New quick measure

loads a screen and we will search in totals, cumulative total choose total accumulated 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.

the accumulated you put it as 2nd value

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

Ss.