Dynamic Date diff grouping visualization

[Anonymous]

Hi All,

I managed to creat two measuser, one that calculates the diffrence in days bettwen the selected report date and clouser date, and grouping PastDue/Upcoming. Based on this PastDue/Upcoming, I would like to build a bar chart with  PastDue/Upcoming on the x-axis and the number of IDs on the y-axis. 

DateDiff = DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)

PastDue/Upcoming = SWITCH (

TRUE,

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) < -60 , "Past Due 90",

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) >= -60 && (DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) < -30, "Past Due 60",

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) >= -30 && (DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) <= 0, "Past Due 30",

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) > 60 && (DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) <= 90, "Upcoming 90",

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) <= 60 && (DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) > 30, "Upcoming 60",

(DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) <= 30 && (DATEDIFF(SELECTEDVALUE(ReportDate[End of Month]), SELECTEDVALUE(IDs[Clouser Date]), DAY)) > 0, "Upcoming 30",

BLANK()

)

I would like to add a pbix file, but as a new user I can't. Hope screanshots and code will be enough.

Best regards 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Below is my table1:

Below is my table2:

The following DAX might work for you:

PastDue/Upcoming1 = SWITCH (

TRUE,

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) < -60 , "Past Due 90",

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) >= -60 && (DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) < -30, "Past Due 60",

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) >= -30 && (DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) <= 0, "Past Due 30",

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) > 60 && (DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) <= 90, "Upcoming 90",

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) <= 60 && (DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) > 30, "Upcoming 60",

(DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) <= 30 && (DATEDIFF(SELECTEDVALUE('Table (2)'[End of month]), SELECTEDVALUE('Table'[Clouser Date]), DAY)) > 0, "Upcoming 30",

BLANK()

)

you can try to use column.

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.