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