Forum Discussion

joseluis1969's avatar
joseluis1969
Frequent Visitor
7 years ago
Solved

Date filter in Calculate function

Good afternoon:
I am trying to make a measure that reflects in a table the total cost Budgeted by day, month and year ONLY UNTIL the last date of the "Real" table. I have (among others) the following tables in my data model:
- Calendar
- Ppto: Table of costs budgeted from 01/01/2019 to 31/12/2019
- Real: Table of Actual Accounting costs from 01/01/2019 to 28/06/2019
The result that I intend to obtain must be equal to what the following measure would give me:

Total = calculate(sum(Ppto[importe]);filter(Calendario;Calendario[Date]<=VALUE(28/06/2019)))

 

The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table).

For example, if I use this measure: "calculate (sum (Ppto [importe); filter (Calendario; Calendario [Date] <= max (Real [date]))" the result I get is incorrect because in the table "Real" there are not all the dates that exist in the" Calendario "table.

Thanks in advance,

José Luis

 

  • Anonymous's avatar
    Anonymous
    7 years ago

    "The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table)."

     

    Well, that's rather simple:

    [Very Last Date in Real] :=
calculate(
    max( Real[Date] ),
    all( Real )
)

    and here's how you'd use it:

    var __lastDateInReal = [Last Date in Real]
return
calculate (
	sum ( Ppto[importe] );
	KEEPFILTERS( Calendario[Date] <= __lastDateInReal )
)

    if you want to intersect filters on Calendario that are coming from the outer context... and this is probably what you want to do.

     

    Best

    Darek

6 Replies

  • avanderschilden's avatar
    avanderschilden
    Resolver I
    7 years ago

    Hello,

     

    What about this?

     

    Measure =

    VAR MaxDateBalance = MAX('Table'[Date])

    RETURN CALCULATE(SUM('Table'[Column]),FILTER(ALL('Date'),'Date'[Date]<MaxDateBalance))
    • joseluis1969's avatar
      joseluis1969
      Frequent Visitor
      7 years ago

      Hello:

      Thank you very much for your answer. The measurement you indicate does not work correctly, I try to explain it with the following image:

      Any Ideas?

      • avanderschilden's avatar
        avanderschilden
        Resolver I
        7 years ago

        Not sure if I understand you correctly, but the following measure is also displaying values for periods that don't exisit in the actuals table;

         

        Cumulative Actual Amount =

        CALCULATE ( SUM ( Actual[Actual_Amount] ) ,
        FILTER ( ALL ( 'Calendar' ) , 'Calendar' [Date] <= MAX ( 'Calendar'[Date] ) ) )


  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    "The question is: With what dax expression can I substitute VALUE (28/06/2019) for the FINAL DATE of the "Real" table ? (28/06/2019 is the last date of the "Real" table)."

     

    Well, that's rather simple:

    [Very Last Date in Real] :=
calculate(
    max( Real[Date] ),
    all( Real )
)

    and here's how you'd use it:

    var __lastDateInReal = [Last Date in Real]
return
calculate (
	sum ( Ppto[importe] );
	KEEPFILTERS( Calendario[Date] <= __lastDateInReal )
)

    if you want to intersect filters on Calendario that are coming from the outer context... and this is probably what you want to do.

     

    Best

    Darek

    • answeriver's avatar
      answeriver
      Helper IV
      6 years ago

      Hi all,

       

      How i can use formula which help me make sameperiod last month.

      Now i use this, but it's error (red text)

      SPLM = CALCULATE([mln st];REPORT_IMS[VISIT YEAR]=YEAR(TODAY());'REPORT_IMS'[VISIT MONTH]=MONTH(TODAY())-1;REPORT_IMS[WORKING DAY]<=CALCULATE(MAX(REPORT_IMS[WORKING DAY];DATESMTD(REPORT_IMS[VISIT DATE]))))
       
      How i can fix it?
       
      Basically i need sales (mln st) in previous month in same count of working days in this month
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello, Thank you for the help.

      In my case, I want to get the max date depending on the filter and go back a year.

      For example, if the selected date on the Real date filter is 6/22/2022. I want to filter another customer table where customer date equals 6/22/2021.