Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RossBateman96
Helper I
Helper I

Ading columns to a table visual based on a slicer

Hi

I've created the following table, which is filtered by a posting date.

RossBateman96_0-1732710627899.png

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.

RossBateman96_1-1732710730644.png

 

Any help would be appreciated.

 

Thanks,

Ross

 

1 ACCEPTED SOLUTION
Jai-Rathinavel
Super User
Super User

@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:

JaiRathinavel_0-1732712914433.png

 

 

Did I answer your question? If yes, please mark my post as a solution.

 

Thanks,

Jai

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

13 REPLIES 13
RossBateman96
Helper I
Helper I

@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_0-1732726823688.png

 

Jai-Rathinavel
Super User
Super User

@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:

JaiRathinavel_0-1732712914433.png

 

 

Did I answer your question? If yes, please mark my post as a solution.

 

Thanks,

Jai

 




Did I answer your question? Mark my post as a solution!

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_0-1732718263857.png

 

@RossBateman96 Please remove .[Day] after the DueDate column and check the DaysOverdue value.

 

Thanks,

Jai




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@Jai-Rathinavel the value has changed but it still isn't calculating properly 

RossBateman96_0-1732719594546.png

 

Thanks

@RossBateman96  Try Replacing DaysOverdue with below DAX

DaysOverdue = DATEDIFF(SELECTEDVALUE('Table'[DueDate]),[Selected Date],DAY)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@Jai-Rathinavel it returned a blank output after making this change! Any other suggestions?

 

RossBateman96_0-1732723172527.png

 

@RossBateman96 can you show me the SelectedDate measure?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@Jai-Rathinavel 

RossBateman96_0-1732724776260.png

 

From a table set up as per the below

RossBateman96_1-1732724831655.png

 

@RossBateman96 Can your create a disconnected calculated table as I mentioned above and use that tables 'column in the slicer and SelectedValue measure ?




Did I answer your question? Mark my post as a solution!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Laxmanjatoth
Resolver I
Resolver I

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 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.