Forum Discussion

Henricus's avatar
Henricus
Frequent Visitor
3 years ago

Using mutltiple dates in a table

Hi, I have a question using dates in a table.

I have a dataset consisting of the following columns:

name, date, score

 

The dates range is different per dataset, but it's done per day, so each row has a day of a month/year assigned to it. 
So the names are duplicate but the date and score differ.

 

I would like to have a table with the following setup:

 

I hope it's possible, but I'm lost.

Hope someone can guide me to a (possible) solution.

 

Thanks!
Dennis

 

Need Help

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Henricus So your data looks like this?

    Name Date Score
    test 1/1/2022 12
    test 2/1/2022 13
    test 3/1/2022 13
    test2 1/1/2022 12
    test2 2/1/2022 11

     

    • Henricus's avatar
      Henricus
      Frequent Visitor
      3 years ago

      Greg_Deckler my apologies, was away for a couple of days.

      My data is like that. But what I want in my report is a column with a heading ofr the first of lets say jan, 1st of feb, etc. and then a columns with the change.

      But I don't seem to get the data from the specific dates in the columns.
      Should I create a measurement for that?

      Thanks in advance

      Kind regards,
      Henricus

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      Henricus OK, see attached PBIX below signature. The measure is:

      change = 
    VAR __Score = SUM('Table'[Score])
    VAR __Date = MAX('Table'[Date])
    VAR __Name = MAX('Table'[Name])
    VAR __PreviousDate = MAXX(FILTER(ALL('Table'),[Name] = __Name && [Date] < __Date),[Date])
    VAR __PreviousScore = SUMX(FILTER(ALL('Table'),[Name] = __Name && [Date] = __PreviousDate),[Score])
RETURN
    IF(__PreviousScore = BLANK(), BLANK(), __Score - __PreviousScore)
      • Henricus's avatar
        Henricus
        Frequent Visitor
        3 years ago

        Thanks for your reply Greg_Deckler!

        I'll check it out tomorrow on my working computer. 
        One other question though, How do I get those date columns? They don't seem to work.

        Like, can I get columns starting on the first of the month for example?

         

         

        Thanks again.
        Kind regards,
        Henricus