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.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |