Forum Discussion
Difference between current value and previous value respect to date.
Hello Everyone, I am fresher in Power BI. I am facing the problem following below. It would be more than a great help if, someone help me in these problem. Appreciate your support.
Active Difference = [Active] - [Active Daily]
where,
Active = SUM('P9'[Active])/DISTINCTCOUNT('P9'[Date])
Active Daily = CALCULATE( [Active], DATEADD(P9[Date], -1, DAY ))
Problem : If I created line chart without date heirarchy (just a normal Date on X - axis) it will plot the data value properly but when I use date heirarchy it will shows the value of [Active] of that date. For the information, I also want to mark difference month wise. Please help me with the problem.
Hi krishna_chitrak ,
Thanks for the reply from Uzi2019 , please allow me to provide another insight:
You need to create a date table and then model the one-to-many relationship. Then create a formula similar to the following to be able to correctly use the date hierarchy as the x-axis.
M_activce = VAR active_ = CALCULATE ( SUM ( 'Table'[Active] ) ) VAR last_month = CALCULATE ( SUM ( 'Table'[Active] ), DATEADD ( 'Date'[Date], -1, MONTH ) ) RETURN active_ - last_monthM_inactivce = VAR inactive_ = CALCULATE ( SUM ( 'Table'[Inactive] ) ) VAR last_month = CALCULATE ( SUM ( 'Table'[Inactive] ), DATEADD ( 'Date'[Date], -1, MONTH ) ) RETURN inactive_ - last_monthBest Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi krishna_chitrak ,
Thanks for the reply from Uzi2019 , please allow me to provide another insight:
You need to create a date table and then model the one-to-many relationship. Then create a formula similar to the following to be able to correctly use the date hierarchy as the x-axis.
M_activce = VAR active_ = CALCULATE ( SUM ( 'Table'[Active] ) ) VAR last_month = CALCULATE ( SUM ( 'Table'[Active] ), DATEADD ( 'Date'[Date], -1, MONTH ) ) RETURN active_ - last_monthM_inactivce = VAR inactive_ = CALCULATE ( SUM ( 'Table'[Inactive] ) ) VAR last_month = CALCULATE ( SUM ( 'Table'[Inactive] ), DATEADD ( 'Date'[Date], -1, MONTH ) ) RETURN inactive_ - last_monthBest Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Adamk Kong,
It's a very helpful solution. Want to ask that is it possible to add buttons and switch to day and month difference?
Hi krishna_chitrak
For better modeling you should create calendar / date column.If you dont want day wise data then select month from date hierarchy by removing other level of hierarchy.
for month on month growth you can use formula below for Previous month
MOM= CALCULATE(SUM('table'[SALES]),DATEADD('table'[DATE],-1,MONTH))if you have continues date then only above formula will work otherwise try below formula
MOM2=CALCULATE(sum(table[Sales]),PARALLELPERIOD('table'[Date],-1,MONTH)then you can find the difference between these 2 values by subtracting
Measure Diff= Sum(Sales)- [MOM]I hope this resolved your issue!Or you can try Ribbon chart that kind of solve your issue.
it auto shows month wise varience.
Hey Uzi2019, appreciate for your feedback.
I got the output following below and it's not showing the difference. IN customer = Active (2nd graph).
Thank-you
Hi krishna_chitrak ,
Are you able to provide the test data used in your case? It is convenient for me to answer your question as soon as possible.
Best Regards,
Adamk KongHi Adamk Kong,
Sorry for the delay in reply. I attached the excel file link below.
https://docs.google.com/spreadsheets/d/1muxTPW_QjigLCjUtBomWsHRKPH6CplQt/edit?usp=sharing&ouid=117139710448398031186&rtpof=true&sd=true
Thank-you
Best Regards,
Krishna Chitrak
