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 followin...
  • 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

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.