Forum Discussion

Moushmi's avatar
Moushmi
Frequent Visitor
1 year ago
Solved

Getting max date from date filter using DAX measure but ignoring in matrix calculated column

  Hi, I am using Direct Query I am trying to create a Matrix to display financial report. My matrix should show, Row: Account code Value: Amount Column: Beginning balance ( balance of pri...
Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    Moushmi , You need build balances using cumulative method. If the day is driver then you can have like 

     

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[date] <min(date[date])))

     

    same way I can use Year of FY (Numeric)

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[Year] <min(date[Year]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[Year] <min(date[Year])))

     

    Same way End of period is 

     

    EOP= CALCULATE(SUM(Table[In]),filter(all(date),date[date] <=Max(date[date]))) -
    CALCULATE(SUM(Table[Out]),filter(all(date),date[date] <= Max(date[date])))

Moushmi's avatar
Moushmi
Frequent Visitor
1 year ago

Hey, by this method I can only show previous year and current year. I want to show beginning balance which will have data for years prior to current year. for eg. if selected date in slicer is 06/30/2025 my beginning balance should show sum of amount for years 2024, 2023, 2022,....

 

Also, I don't want my current year to just be current year in the column lable. It needs to be company codes in the column to display current year balance.

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    Moushmi , You need build balances using cumulative method. If the day is driver then you can have like 

     

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[date] <min(date[date])))

     

    same way I can use Year of FY (Numeric)

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[Year] <min(date[Year]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[Year] <min(date[Year])))

     

    Same way End of period is 

     

    EOP= CALCULATE(SUM(Table[In]),filter(all(date),date[date] <=Max(date[date]))) -
    CALCULATE(SUM(Table[Out]),filter(all(date),date[date] <= Max(date[date])))

    • v-menakakota's avatar
      v-menakakota
      Community Support
      1 year ago

      Hi Moushmi  ,

      Thanks for reaching out to the Microsoft fabric community forum.

       

      I would also take a moment to thank amitchandak  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

      Best Regards, 
      Community Support Team 

      • v-menakakota's avatar
        v-menakakota
        Community Support
        1 year ago

        Hi Moushmi ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

        Thank you.