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 prior years based on date selected in date filter), Current year balance should be expanded with company codes, hierarchy with department code. (Pasted below... I hidded the names but i need to show like this)

Problem is when I select any date from previous year.. I am only seeing beginning balance column. I think my calculated column is not capturing the Post Date from filter. 

 

Year Category =
VAR SelectedYear = YEAR(MAX('GL'[Post_Date]))

RETURN
    SWITCH(
        TRUE(),
        YEAR(GL[Post_Date]) = SelectedYear, 'GL'[Company_Code] &" - "& 'GL'[Company_Name],
        YEAR(GL[Post_Date]) < SelectedYear, "Beginning balance",
        BLANK()
    )
 
can you tell me how can I achive showing beginning balance and current balance based on user date selection?
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])))

6 Replies

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    1 year ago

    Hi, Moushmi ,
    well, i would scratch your calculated column as it's column and static, so it can'T really work dynamically in report upon selection.

     

    And just use this (as a measure) for current year and previous year.

     

    YTD = TOTALYTD ( [measure], 'calendar'[Date] )

YTD PY =
CALCULATE (
    [YTD],
    SAMEPERIODLASTYEAR ( 'calendar'[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
      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