topic Re: I need to create a date to use as VAR in DAX Commands and Tips

I need to create a date to use as VAR

arossello — Wed, 26 Aug 2020 09:26:13 GMT

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

Re: I need to create a date to use as VAR

amitchandak — Wed, 26 Aug 2020 09:53:41 GMT

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
))

Re: I need to create a date to use as VAR

arossello — Wed, 26 Aug 2020 10:56:55 GMT

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😥

Re: I need to create a date to use as VAR

amitchandak — Wed, 26 Aug 2020 14:48:06 GMT

arossello , try like

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

Re: I need to create a date to use as VAR

arossello — Thu, 27 Aug 2020 11:19:17 GMT

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

Re: I need to create a date to use as VAR

AntrikshSharma — Thu, 27 Aug 2020 12:00:15 GMT

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

Re: I need to create a date to use as VAR

arossello — Thu, 27 Aug 2020 13:59:06 GMT

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