Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arossello
Frequent Visitor

I need to create a date to use as VAR

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:

arossello_0-1598431748159.png

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:

 

arossello_1-1598432628632.png

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:

arossello_2-1598433814363.png

Thank you very much for your help

 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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

 

View solution in original post

6 REPLIES 6
AntrikshSharma
Community Champion
Community Champion

@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

 

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

amitchandak
Super User
Super User

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

Thank you very much for your answer,

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

arossello_0-1598436417820.png

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😥

 

 

@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?

@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.

 

arossello_0-1598526575585.png

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

arossello_1-1598526682517.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors