Forum Discussion

farhanerd's avatar
farhanerd
Frequent Visitor
5 years ago

Calculate Daily Differences Based on Dates and Changing Province

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:

idid_provinsiterkonfirmasisembuhmeninggaltanggal
182641300772273513208/24/2021
1522003/15/2020


Thank you!
Farhan 🙂

Need Help
Show and Tell
Tutorial Requests

5 Replies

    • farhanerd's avatar
      farhanerd
      Frequent Visitor
      5 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.