Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have created Running Total based on calendar date.
But due to requirement i would like to have Year,Quarter,Month,Weekday,Weeknum in slicer.
Thus i created calculated table for that.
Running Total was working fine when we give date column from Calendar table but i would like to give "Period" instead of that so that based on slicer selection it should get reflected.
This was the running Total formula which i used.
Now my question is can we create a measure based on the values selected in "Period".
For Eg.,
If period=Year then "need a measure value which holds running total Year wise"
else if Period = Quarter then "need a measure value which holds running total Quarter wise"
else if Period = Month then "need a measure value which holds running total Month wise"
and so on
I tried to create switch statement in column but count is not matching properly and in Measure i coulnd't able to write Case statement since it is showing error.
Any Help would be very greatful.
Thanks in advance
Solved! Go to Solution.
Hi
Maybe this can help you.
You need a calendar table to make this work.
(The data you provided, create blank query in PowerBI and copy paste)
Try this union.
Acc after max value on date
You need to solve the sorting problem, but this works 🙂
/Adam
Hi
Maybe this can help you.
You need a calendar table to make this work.
(The data you provided, create blank query in PowerBI and copy paste)
Try this union.
Acc after max value on date
You need to solve the sorting problem, but this works 🙂
/Adam
/Adam
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Guys,
@v-jayw-msft ,@Greg_Deckler ,@v-lionel-msft ,@Adamtall ,@Anonymous
Any help will be greatful onn the below issue.
Thanks in Advance
Formula seems correct. If you are use it with month or qtr or year, is it not showing cummlative results.
Or for Qtr you want rest value after QTR
In such case you have to use DatesMTD, datesqtd etc or totalmtd ..
also refer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
Refer formula's
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |