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.
Hi Experts,
I'm calculating a measure on a date and below in the DAX for measure Overdue Amount it is today. My goal is to calculate this same measure for all the dates of a Selected Month. I need to show this in a Bar Chart as a trend of Overdu Amount. How to achieve this and I do not want the selection of Calendar Month should impact entire data set rather it should only give dates to the following DAX formula.
I would appreciate your inputs.
@ashrat001 , Try measure like with date table
Date table not joined
Overdue Amount =
VAR LastDay= maxx(allselected('Date') , 'Date'[Date]) //should return last date in selected period
RETURN
SUMX(
FILTER(
'Table',
'Table'[Net due date.Net due date Level 01]< LastDay && 'Table'[Item Status.Item Status Level 01] = "O"
),
'Table'[Debit amt in LC]
)
Date table joined
Overdue Amount =
VAR LastDay= maxx(allselected('Date') , 'Date'[Date]) //should return last date in selected period
RETURN
calculate(
FILTER(sum('Table'[Debit amt in LC])
All('Date'),
'Date'[Date]< LastDay),
FILTER(
'Table','Table'[Item Status.Item Status Level 01] = "O"
)
)
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.
Hi Amit,
Thanks for your reply. As per your reply, I have modified my measure. But unfortunately I'm not getting the desired outcome. As the MAX funciton is giving today's date for selected month and therefore filtering data on today's date. I want accumulative values for selected Month dates on each date say November's date should have values for 1 Nov , 2 Nov till 27 Nov so on.
Do you want that the month filter should not impat your metric and evrytime it gives the same answer irrespective of any months seleted ?
Hi Kumar,
Thanks for your reply. I want to replace Today() with dates of selected Calendar Month and this selected month should only give dates to the formula and should not impact the table/query. Thanks!
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |