I need to create a date to use as VAR

[arossello]

Hello community,

I need to create a measure that has a fixed variable, to perform the future calculation,

Here is the process I am following:

The filters I have are the following:

I need to calculate two measures the sales you make to date and on the other hand the reserved sales that I have in the future, depending on the date selected in Date

This is the result I want to get:

The measures used are the following:

Sales actual = SUM(f_sales[base])

Future Sales = 
VAR FECHAINFORME = DATE(2020,08,22)
RETURN
CALCULATE(
SUM(f_reserva[adr_dia])
,f_reserva[estado_reserva_id]= 1
,f_reserva[externa]= 0
, FILTER(
   f_reserva
   ,f_reserva[fecha]>= FECHAINFORME
))

As you can see I am using the DATE () function, but what I want is for this date to be the maximum selected in the Date filter.

I have tried replacing DATE with the following measure:

day_report_total = CALCULATE(
    MAX('Calendario'[Date])
    ,ALLSELECTED('Calendario')
)

But the result is the following:

Thank you very much for your help

[AntrikshSharma · Accepted solution]

@arossello Attaching the file below my signature. I have added a disconnected slicer to filter the dates, Let me know if that is what you want.

Future Sales =
VAR DisMaxDate =
    CALCULATE (
        MAX ( 'Disconnected Calendario'[Date] ),
        ALLSELECTED ( 'Disconnected Calendario'[Date] )
    )
VAR VisualMaxDate =
    MAX ( Calendario[Date] )
VAR Result =
    SUMX (
        ADDCOLUMNS (
            FILTER ( VALUES ( Calendario[Date] ), Calendario[Date] > DisMaxDate ),
            "@Sales", [Total Sales]
        ),
        [@Sales]
    )
RETURN
    Result

Current Sales = 
VAR DisMaxDate =
    CALCULATE (
        MAX ( 'Disconnected Calendario'[Date] ),
        ALLSELECTED ( 'Disconnected Calendario'[Date] )
    )
VAR Result =
    SUMX (
        ADDCOLUMNS (
            FILTER ( VALUES ( Calendario[Date] ), Calendario[Date] <= DisMaxDate ),
            "@Sales", [Total Sales]
        ),
        [@Sales]
    )
RETURN
    Result

[arossello]

@AntrikshSharma  Thank you very much, for the solution provided it is great

[amitchandak]

@arossello , this should have worked. Try like this once

Future Sales =
VAR _FECHAINFORME = maxx(ALLSELECTED('Calendario'),'Calendario'[Date])
RETURN
CALCULATE(
SUM(f_reserva[adr_dia])
,f_reserva[estado_reserva_id]= 1
,f_reserva[externa]= 0
, FILTER(
f_reserva
,f_reserva[fecha]>= _FECHAINFORME
))

[arossello]

Thank you very much for your answer,

I have had a problem if I apply your measure, the result is the following:

The amount is that of 08/22/2020, as you can see future months do not appear.

I don't know what I can do anymore😥

[amitchandak]

@arossello , try like

Future Sales =
VAR _FECHAINFORME = maxx(ALLSELECTED('Calendario'),'Calendario'[Date])
RETURN
CALCULATE(
SUM(f_reserva[adr_dia])
,f_reserva[estado_reserva_id]= 1
,f_reserva[externa]= 0
, FILTER(
all(f_reserva)
,f_reserva[fecha]>= _FECHAINFORME
))

Can you share sample data and sample output in table format?

[arossello]

@amitchandak  , Hello

I am attaching the pbix, so you can see the behavior of the data.
The biggest problem is that when we select a date that is not the end of the month it works well.

But if we select the last day of the month this is the result, as you can see the measure does not work

https://drive.google.com/file/d/1FhVeUV89AqOY-Z2EekVraUwyAU9KhkoW/view?usp=sharing