Forum Discussion

lionx's avatar
lionx
Icon for Helper I rankHelper I
3 years ago

Calculated Column and measure by selected date

 I have invoicing table as described by the picture below. Data includes information related to invoice such as Invoice Date, Amount_LCY, Remaining_Amount_LCY. Please help me to solve this.
Conditions should be:
  • If the Posting_Date > Selected_Date, values of the New_Remaining_Amount_LCY = Remaining_Amount_LCY
  • If the Posting_Date <= Selected_Date, values of the New_Remaining_Amount_LCY = Amount_LCY but excluding the makeup transactions that equal to zero.
I try to calculate a New_Remaining_Amount_LCY (Column or Measure) by the syntax below to get the result as in the picture, but it does not work.  
Many thanks in advance!
 
New_Remaining_Amount_LCY =
IF(
    [Posting date] >= [Selected Date],
    [Remaining Amt LCY],
    CALCULATE(
        SUM(Invoice[Amount_LCY]),
        FILTER(Invoice,
            ALLEXCEPT(Invoice,Invoice[Customer_No]) &&
            [Posting date] < [Selected Date]
        )
    )
)
 

 

 
 
Need Help

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

    You're making the same mistake as so many have before you. Such calculations should be performed in Power Query, not DAX. Please do the right thing and create a table that has the right calcs for each invoice in the correct time order. Then, once you've created such a table, create simple measures that will only fetch data from such a table. You're save yourself a lot of grief and hair-pulling.

    • lionx's avatar
      lionx
      Icon for Helper I rankHelper I
      3 years ago

      Hi @daXtrrme
      Thank you for your response.

      I have try calculated using M-query when I fetched data from source but it doesn't work. 

      I think this is because the Selected_Date get from slicer using Calender[Date] when it chosen back to the specific date in the Date slicer. 

      + If I choose Selected_Date is Today or After, it's totally fine. 

      + It just happened when I choose a specific date in the past (for example: 16-Nov-2022). The issue here is Remaining_Amount_LCY that made up from Invoice and Payment is not corresponded with the choosen past date (16-Now-2022). Remaining_Amount_LCY is always up to date at present. I attached sample data below.

      .pbix file with sample data 

      Could you please help me to have a look?

      Thank you for your time and support.