Forum Discussion

mclawler's avatar
mclawler
Helper III
2 years ago
Solved

Previous Year Dateadd Excluding Data

When I use this measure:

 

VAR AdjustedTable =
    ADDCOLUMNS(
        VALUES('PwBi LoanData'[Product (groups)]),
        "AdjustedValue",
        IF(
            'PwBi LoanData'[Product (groups)] = "Auto",
            CALCULATE(COUNT(AutoLoanMatrix[AmountAdvanced]),DATEADD('DateTable'[Date],-1,YEAR)),
            CALCULATE(COUNT('PwBi LoanData'[Amount Booked]),DATEADD('DateTable'[Date],-1,YEAR))
        )
    )
RETURN
SUMX(AdjustedTable,[AdjustedValue]) + 0
 
I get these results:
 

 

But when I use this measure by itself:

 

CALCULATE(COUNT('PwBi LoanData'[Amount Booked]),DATEADD('DateTable'[Date],-1,YEAR))
 
I get these results:
 

 

 

The former DAX is correctly calculating the Auto row, but it's dropping counts for all the other rows below it.  Same thing happens with Previous YTD.

 

Any ideas?  Thank you for your time

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mclawler ,
    For your total and Auto outside the value is not correct, we can use the ISINSCOPE function, the total for a correct sum, Auto outside the value of some can not get, we can try to use a few more defined variables to get a valid result, I hope my solution is helpful to you!

    AmountBooked COUNT(Copy) = 
VAR AutoCount=CALCULATE(
COUNT('AutoLoanMatrixDummy'[AmountAdvanced]),'LoanDataDummy'[Product (groups)]="Auto")
VAR OtherCount=COALESCE(
CALCULATE(COUNT(LoanDataDummy[Amount Booked]),ALLEXCEPT('LoanDataDummy','LoanDataDummy'[Product (groups)])),0)
VAR C=CALCULATE(COUNT(LoanDataDummy[Amount Booked]),FILTER('LoanDataDummy','LoanDataDummy'[Product (groups)]="Auto"))
RETURN
IF(
    ISINSCOPE('LoanDataDummy'[Product (groups)]),
    SWITCH(
        TRUE(),
        MAX('LoanDataDummy'[Product (groups)])="Auto",
        AutoCount,
        OtherCount),
        AutoCount+OtherCount-C)



     

     I have updated your data to the results you need, if you have any other questions you can check the PBIX file I have uploaded, I would be grateful if my solution can help you solve your problem!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies