Calculate Daily Differences Based on Dates and Changing Province

[farhanerd]

Hello, I need to make a line graph actually like this picture below, but with a different table

It is quite actually simple on making that graph since id_provinsi is not changing and I am still able to get the difference between each row on each column

I need the solution if id_provinsi is changing daily in ascending way based on the earliest date like this picture below:

id	id_provinsi	terkonfirmasi	sembuh	meninggal	tanggal
18264	1	30077	22735	1320	8/24/2021
15	2	2	0	0	3/15/2020

Thank you!
Farhan 🙂

[v-xiaotang]

Hi @farhanerd 

sorry for the late reply, try this

DiffKonfirmasi = 
VAR Konfirmasi_PreviousDate =
    MAXX (
        FILTER (
            'EWS covid_date_clean',
            'EWS covid_date_clean'[id_provinsi]
                = EARLIER ( 'EWS covid_date_clean'[id_provinsi] )
                && 'EWS covid_date_clean'[tanggal] < EARLIER ( 'EWS covid_date_clean'[tanggal] )
        ),
        'EWS covid_date_clean'[tanggal]
    )
VAR Konfirmasi_Previous =
    MAXX (
        FILTER (
            'EWS covid_date_clean',
            'EWS covid_date_clean'[tanggal] = Konfirmasi_PreviousDate
                && 'EWS covid_date_clean'[id_provinsi]
                    = EARLIER ( 'EWS covid_date_clean'[id_provinsi] )
        ),
        'EWS covid_date_clean'[Sembuh]
    )
RETURN
    'EWS covid_date_clean'[Sembuh] - Konfirmasi_Previous

Best Regards,

Community Support Team _Tang

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

[Greg_Deckler]

@farhanerd See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[farhanerd]

This is my expected result

[farhanerd]

Hi @Greg_Deckler, I tried to put that query on my table but the result seems doesn't as what I expected.

Am I put the query wrong?
I'm kinda new here and after lots and lots of trying attempts I still didn't get the answer I want, I need your guidance @Greg_Deckler 

[Greg_Deckler]

@farhanerd I am guessing that there is some kind of identifier that identifies which rows are "grouped" together. For example, I see a bunch of March 16th rows in your data. Thus, I am guessing that there are probably multiple March 15th rows. The code you have will find March 15th for the Previous Date. But, it will then look up the largest value on that date, which is apparently 210. So, if  you were looking for 117 instead I think you will need to add more specificity to your FILTER statemetns like && [somecolumn] = EARLIER([somecolumn)

Note that you can also use VAR statements to grab your current values instead of using EARLIER.