Forum Discussion

ThisIsHalloween's avatar
ThisIsHalloween
Helper I
2 years ago
Solved

Table Based Off Date Measure Not Populating

I have a measure that calculates the last date in a table:   Last Date in Receipts CY = MAX(Receipts[BI Date])   I have a second measure that takes that same date and subtracts a year: ...
Need Help
  • ThisIsHalloween's avatar
    ThisIsHalloween
    2 years ago

    I tried this as well hoping that it would work but not luck so I think it's that I am using edate but I don't know what to use instead:

     

     

    PY = 
FILTER (
    Receipts,
    AND (
        Receipts[BI Date] >= DATE ( 2022, 7, 1 ),
        Receipts[BI Date] <= edate([Last Date in Receipts CY],-12)
    )
)

     

ThisIsHalloween's avatar
ThisIsHalloween
Helper I
2 years ago

Whoever comes to see this in the future, here is what I did that worked:

 

 

PY = 

VAR PYDateEnd = 
EDATE(MAX(Receipts[BI Date]),-12)

VAR PYStartDate = 
EDATE(min(CY[BI Date]),-12)

RETURN


FILTER (
    Receipts,
    AND (
        Receipts[BI Date] >= PYStartDate,
        Receipts[BI Date] <= PYDateEnd
        
    )
)

 

The PYStartDate is referencing to take the minimum date in my CY table and subtract out 12. The table wouldn't work when I referenced a measure with EDATE but it worked when I used it as VAR. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ThisIsHalloween 

     

    Glad to hear you solved the problem! Could you please mark your solution as an ANSWER? This will help more users who are facing the same or similar difficulties. Thank you!

     

    Best Regards,
    Yulia Xu

    • ThisIsHalloween's avatar
      ThisIsHalloween
      Helper I
      2 years ago

      Thanks for letting me know! I'm still very new to PowerBI and the Community.