Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 calc...
Need Help
Show and Tell
  • Don_Alexis's avatar
    Don_Alexis
    5 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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