Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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)
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
@Henricus You can use something like DATEVALUE("Jan 2022")
@Henricus Well, if your current data just has the 3 letter month name, create a new calculated column like this:
Date2 = DATEVALUE([Date] & " 2022")
@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 |
@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
User | Count |
---|---|
94 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |