Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
I've created the following table, which is filtered by a posting date.
I need to calculate additional columns as per the highlighted headings in the image below but to do this I think I need to create a measure which shows the value of 31/10/24 although this can change if the slicer changes.
Any help would be appreciated.
Thanks,
Ross
Solved! Go to Solution.
@RossBateman96 You can create a disconnected Calendar date table using the below DAX and take the MAX of the Date column based on the selection
1. Create a calculated table like below: (Don't establish a relationship between these two tables)
Calendar = CALENDAR(FIRSTDATE(Table[DueDate]),LASTDATE(Table[DueDate])
2. Now create a measure to track the Max of the selected date in the slicer
Selected Date = MAX(Calendar[Date])
3. Create all the following measures
DaysOverdue = DATEDIFF(MAX(Table[DueDate]),[Selected Date],DAY)
NoDue = IF( [DaysOverdue] <= 0 , 0 , SUM('Table'[Amounts]))
< 7 = IF(
AND([DaysOverdue] >= 0,[DaysOverdue] <= 7),
[NoDue],0)
8 - 30 = IF(
AND([DaysOverdue] >= 8,[DaysOverdue] <= 30),
[NoDue],0)
30+ = IF( [DaysOverdue] > 30, [NoDue], 0 )
Output:
Did I answer your question? If yes, please mark my post as a solution.
Thanks,
Jai
Proud to be a Super User! | |
@Jai-Rathinavel it looks like it now works but I have another issue where it won't show in the visual which I'll post elsewhere
@RossBateman96 You can create a disconnected Calendar date table using the below DAX and take the MAX of the Date column based on the selection
1. Create a calculated table like below: (Don't establish a relationship between these two tables)
Calendar = CALENDAR(FIRSTDATE(Table[DueDate]),LASTDATE(Table[DueDate])
2. Now create a measure to track the Max of the selected date in the slicer
Selected Date = MAX(Calendar[Date])
3. Create all the following measures
DaysOverdue = DATEDIFF(MAX(Table[DueDate]),[Selected Date],DAY)
NoDue = IF( [DaysOverdue] <= 0 , 0 , SUM('Table'[Amounts]))
< 7 = IF(
AND([DaysOverdue] >= 0,[DaysOverdue] <= 7),
[NoDue],0)
8 - 30 = IF(
AND([DaysOverdue] >= 8,[DaysOverdue] <= 30),
[NoDue],0)
30+ = IF( [DaysOverdue] > 30, [NoDue], 0 )
Output:
Did I answer your question? If yes, please mark my post as a solution.
Thanks,
Jai
Proud to be a Super User! | |
Thanks Jai. I am up to stage 3 trying to create a dyas overdue measure but I can't get it to calculate the correct value. Any ideas?
@RossBateman96 Please remove .[Day] after the DueDate column and check the DaysOverdue value.
Thanks,
Jai
Proud to be a Super User! | |
@RossBateman96 Try Replacing DaysOverdue with below DAX
DaysOverdue = DATEDIFF(SELECTEDVALUE('Table'[DueDate]),[Selected Date],DAY)
Proud to be a Super User! | |
@RossBateman96 can you show me the SelectedDate measure?
Proud to be a Super User! | |
@RossBateman96 Can your create a disconnected calculated table as I mentioned above and use that tables 'column in the slicer and SelectedValue measure ?
Proud to be a Super User! | |
Thank @Jai-Rathinavel this works fine now athough the original table didnt have any relationships with another table
I'll continue through your other steps.
@RossBateman96 If my post have answered your question. Please mark my post as a solution
Thanks,
Jai
Proud to be a Super User! | |
you cant do this with if the slicer changes. be practical and with this date slicing , you can approach with diff solutions which is group measure ,
Thanks
Laxman
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
78 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
58 |