Forum Discussion
DAX help with dates
Hi, I'm struggling with a specific DAX code to create a visual with dates.
The purpose of the visual is to represent succesful customers which obtained a good result in the last x months.
The legend should contains succesful results in the last month / succesful in the last 2 months / succesful in the last 3 months etc.
To calculate this for the current data, it is not that hard. Something like: CALCULATE(DISTINCTCOUNT(Table[Customers], table[LastDateOfObtainedResult] >= DATE(YEAR(today(), MONTH(today()) -X, DAY(today()).
However we need this visual to calculate these previous months for every date on the X-axis. So for 2020/06 we want the amount of customers which obtained a succesful result in the previous month, last 2 months, last 3 months etc.
How can i make this dynamic so the calculation happens for every date on the x-as? Please would appreciate any help, I cannot share my data since it is highly confidential. Thanks!
NielsDW
I assume you want to create a slicer to change the date range dynamically, like last 1 month, 2 months, 3 months, etc.In this case, you may create a new table usiong enter data to create a slicer column with values, 1,2,3,4,etc. And use this column create a slicer on the report page. Then just replace the X to selectedvalue() in the dax.
CALCULATE(DISTINCTCOUNT(Table[Customers], table[LastDateOfObtainedResult] >= DATE(YEAR(today(), MONTH(today()) - selectedvalue(newtable[slicer column]), DAY(today()).
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
2 Replies
NielsDW , with help from date table and time intelligence
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))2nd last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],Eomonth(MAX('Date'[Date ]),0),-2,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],Eomonth(MAX('Date'[Date ]),0),-3,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
NielsDW
I assume you want to create a slicer to change the date range dynamically, like last 1 month, 2 months, 3 months, etc.In this case, you may create a new table usiong enter data to create a slicer column with values, 1,2,3,4,etc. And use this column create a slicer on the report page. Then just replace the X to selectedvalue() in the dax.
CALCULATE(DISTINCTCOUNT(Table[Customers], table[LastDateOfObtainedResult] >= DATE(YEAR(today(), MONTH(today()) - selectedvalue(newtable[slicer column]), DAY(today()).
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
