Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a requirement to create a metric using datesinperiod. I've tested it and it works fine against a single date hard coded as a reference..example Max(cxldate.date). I would like the metric to work if the user chooses a different date...Max(acctdate.date). What is the best/more efficient approach to making the 'reference date' variable dynamic depending on which date field is being used in a visual? Basically, I need the reference date to be dynamic so the metric works across multilple date fields.
Much appreciated!
Solved! Go to Solution.
Hi @codyraptor
For your question, here is the method I provided:
Here's some dummy data
"Table"
If you want to dynamically select the maximum date and calculate it, try the following. Create a measure:
Measure =
var max_date =
IF(
ISFILTERED('Table'[Date]),
max('Table'[Date]),
BLANK()
)
RETURN
CALCULATE(
SUM('Table'[Values]),
DATESINPERIOD(
'Table'[Date],
max_date,
-1,
MONTH
)
)
Here is the result
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @codyraptor
For your question, here is the method I provided:
Here's some dummy data
"Table"
If you want to dynamically select the maximum date and calculate it, try the following. Create a measure:
Measure =
var max_date =
IF(
ISFILTERED('Table'[Date]),
max('Table'[Date]),
BLANK()
)
RETURN
CALCULATE(
SUM('Table'[Values]),
DATESINPERIOD(
'Table'[Date],
max_date,
-1,
MONTH
)
)
Here is the result
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think I tried that solution but maybe I missed something. What happens if they filter the year or month rather than the 'date' field. Will that still work?
Hi @codyraptor
The DATESINPERIOD function is a DAX function that returns a single-column date table containing a specified start date and date interval. The date format allowed by this function is the ISO 8601 standard, which is yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss.
If you want to filter based on month, try the following:
Measure 2 =
var max_day =
IF(
ISFILTERED('Table'[Date]),
MAX('Table'[Date]),
BLANK()
)
RETURN
CALCULATE(
sum('Table'[Values]),
FILTER(
ALL('Table'),
MONTH('Table'[Date]) = MONTH(max_day)
)
)
Here is the result.
Please see page 2 of the attachment.
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |