Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have dax measue that works but I beleive the dates are static and would need to be manually updated for the next months. I need the dates to automatically change; From: DATE(2022,05,01), DATE(2023,04,30) To DATE(2022,06,01), DATE(2023,05,30) for the next month. Any help would be greatly appricated.
Here is the full measure:
Solved! Go to Solution.
Sorry, I misread your starting date, it is in 2022 so that would look like this which makes more sense.
2023 Inforce =
VAR _Start = EOMONTH ( TODAY(), -13 ) + 1
VAR _End = EOMONTH ( TODAY(), -1 )
RETURN
CALCULATE (
COUNTA ( 'AISCVGP'[ACCUS#] ),
AISCVGP[ACSTA] = "ACT",
DATESBETWEEN ( 'Calendar'[Date], _Start, _End )
)
I use the VAR just to make it easier to read in this case but you could write the measure like this as well.
2023 Inforce =
CALCULATE (
COUNTA ( 'AISCVGP'[ACCUS#] ),
AISCVGP[ACSTA] = "ACT",
DATESBETWEEN (
'Calendar'[Date],
EOMONTH ( TODAY (), -13 ) + 1,
EOMONTH ( TODAY (), -1 )
)
)
Sorry, I misread your starting date, it is in 2022 so that would look like this which makes more sense.
2023 Inforce =
VAR _Start = EOMONTH ( TODAY(), -13 ) + 1
VAR _End = EOMONTH ( TODAY(), -1 )
RETURN
CALCULATE (
COUNTA ( 'AISCVGP'[ACCUS#] ),
AISCVGP[ACSTA] = "ACT",
DATESBETWEEN ( 'Calendar'[Date], _Start, _End )
)
That worked! Thank you very much. I need to learn how to use the "VAR" dax code! Very helpful!
You can use TODAY() to get a dynamic date range. Next month should it be
DATE(2022,06,01), DATE(2023,05,30) or
DATE(2022,06,01), DATE(2023,05,31) ?
Assuming you want the first of the month and the end of the previous month it would be like this.
2023 Inforce =
VAR _Start = EOMONTH ( TODAY (), -1 )
VAR _End = _Start + 1
RETURN
CALCULATE (
COUNTA ( 'AISCVGP'[ACCUS#] ),
AISCVGP[ACSTA] = "ACT",
DATESBETWEEN ( 'Calendar'[Date], _End, _Start )
)
You also don't need the FILTER('AISCVGP' portion and it is a bad idea to use FILTER over a whole table so I updated that as well.
I tried the measure and it's returning no values. Yes, the next set of dates should be DATE(2022,06,01), DATE(2023,05,31). Any other suggestions? If I use datesbetween I would need to update the dates manually...is that corrcet? Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |