Difference between current value and previous value respect to date.

[krishna_chitrak]

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.

2023,December : 21123

2024, Janurary : 21714

2024, February : 21612

2024, March : 22077

Line Chart will show the growth.

Example: In Month of january growth was (21714 - 21123) = 591

In Month of February growth was (21612 - 21714) = -102

In Month of March growth was (22077 - 21612) = 465

Measure, I used:
              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.

[Anonymous · Accepted solution]

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_month

M_inactivce =
VAR inactive_ =
    CALCULATE ( SUM ( 'Table'[Inactive] ) )
VAR last_month =
    CALCULATE ( SUM ( 'Table'[Inactive] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
RETURN
    inactive_ - last_month

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[krishna_chitrak]

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?

[Anonymous]

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 Kong

[krishna_chitrak]

Hi 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=11713...

Thank-you

Best Regards,
Krishna Chitrak

[Uzi2019]

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!

[krishna_chitrak]

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

[Uzi2019]

Hi @krishna_chitrak 

Or you can try Ribbon chart that kind of solve your issue.

it auto shows month wise varience.