Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
loads a screen and we will search in totals, cumulative total
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.
for a better visualization a segmentation of data with the date/month.
Ss.
@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
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
loads a screen and we will search in totals, cumulative total
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.
for a better visualization a segmentation of data with the date/month.
Ss.