Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am struggling understanding why this Dax isnt working as expected.
I have two tables, date mapping which gives a row per month, and reciepts detail, which is joined on a one (date) to many (reciepts) to the reciept month.
I already have a measure giving me the average days to pay for a selected month, and i'm trying to do the same for the last twelve months based on selected month. I have used the below, but this formula is giving me the same results as the average days to pay for the selected month. In fact, if i take out the filter(all()) arguments, the results dont change.
What am i doing wrong here?
Average Days to Pay LTM =
VAR StartMon =
SELECTEDVALUE ( 'Date Mapping'[Month], 0 )
VAR EndMon =
SELECTEDVALUE('Date Mapping'[Month],-12)
RETURN
CALCULATE (
SUM ( 'Reciepts Detail (F03B14)'[Days to Pay (vs Invoice Date)] ),
FILTER (
ALL ( 'Date Mapping' ),
StartMon>='Date Mapping'[Month] && EndMon<='Date Mapping'[Month]
),
'Reciepts Detail (F03B14)'[Payment?] = "Yes"
)
/ CALCULATE (
COUNTA ( 'Reciepts Detail (F03B14)'[Payment ID] ),
FILTER (
ALL ( 'Date Mapping' ),
StartMon>='Date Mapping'[Month] && EndMon<='Date Mapping'[Month]
),
'Reciepts Detail (F03B14)'[Payment?] = "Yes"
)
Solved! Go to Solution.
This
VAR EndMon = SELECTEDVALUE('Date Mapping'[Month],-12)
doesn't give you the selected month less 12 months. The second parameter to the SELECTEDVALUE function is the default value to use when no month is selected.
There's the DATEADD function you could use (depending on the datatype of the 'Date Mapping'[Month] column. Another alternative would be using DATESINPERIOD as the parameter to CALCULATE rather than FILTER.
This
VAR EndMon = SELECTEDVALUE('Date Mapping'[Month],-12)
doesn't give you the selected month less 12 months. The second parameter to the SELECTEDVALUE function is the default value to use when no month is selected.
There's the DATEADD function you could use (depending on the datatype of the 'Date Mapping'[Month] column. Another alternative would be using DATESINPERIOD as the parameter to CALCULATE rather than FILTER.
Oh - thats embarrasing, i was so focused on the code under the return I wasnt even looking at my variables and of course that would be the reason why! Thanks so much for your help!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |