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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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.

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

      Hi Moushmi ,

      Can you please confirm whether the issue got resolved. If solved can you you please share your solution here,so that other community members can find it easily.

      Thank you.