Ading columns to a table visual based on a slicer

[RossBateman96]

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

[RossBateman96]

@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

[Jai-Rathinavel · Accepted 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

[RossBateman96]

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?

[Jai-Rathinavel]

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

Thanks,

Jai

[RossBateman96]

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

Thanks

[Jai-Rathinavel]

@RossBateman96  Try Replacing DaysOverdue with below DAX

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

[RossBateman96]

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

[Jai-Rathinavel]

@RossBateman96 can you show me the SelectedDate measure?

[RossBateman96]

@Jai-Rathinavel 

From a table set up as per the below

[Jai-Rathinavel]

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

[RossBateman96]

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.

[Jai-Rathinavel]

@RossBateman96 If my post have answered your question. Please mark my post as a solution

Thanks,

Jai

[Laxmanjatoth]

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