Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Problem with ALL

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"
        )

 

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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.

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

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.

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors