Choosing a date and compare a period: filter by different date

[AnaStone_100]

Please for an advice for this analytic scenario, I am not able to solve it. I want to make date comparison in a table by another date. 

For example:

How did Sales look for the for the next 12 months by flight reservation date. So, I want to filter by reservation date.

Lets say,

1.oct.2021 is the reservation date I chose in the filter and I would like to see a monthly comparison by using diffent date (flight check-in date).

Any help much appreciated.

Thank you!

[v-rzhou-msft · Accepted solution]

Hi @AnaStone_100 

I build a sample to have a test. Firstly we need to create an unrelated calendar table to create a slicer.

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Month Yr", FORMAT ( [Date], "MMM-YY" )
)

Then create measures.

Filter = 
VAR _SELECTDATE = MAX('Date'[Date])
VAR _RANGESTART = EOMONTH(_SELECTDATE,-1)+1
VAR _RANGEEND = EOMONTH(_SELECTDATE,+11)
RETURN
IF(MAX('Table'[Date])>=_RANGESTART&&MAX('Table'[Date])<=_RANGEEND,1,0)

Charge = 
SUM('Table'[Value])

Previous Year = 
VAR _CurrentMAXDate = MAX('Table'[Date])
VAR _PreviousMaxDate = EOMONTH(_CurrentMAXDate,-12)
RETURN
CALCULATE(MAX('Table'[Month Yr]),FILTER(ALL('Table'),'Table'[Date]=_PreviousMaxDate))

Basic Previou Charge = 
VAR _CurrentMAXDate = MAX('Table'[Date])
VAR _PreviousMaxDate = EOMONTH(_CurrentMAXDate,-12)
VAR _PreviousMinDate = EOMONTH(_CurrentMAXDate,-13)+1
RETURN
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]<=_PreviousMaxDate&&'Table'[Date]>=_PreviousMinDate))

Previou Charge = SUMX(VALUES('Table'[Month Yr]),[Basic Previou Charge])

Build a table visual with Month Yr column and other measures instead of Filter measure. Then add Filter measure into the Filter field in table visual and set it to show items when value =1.

Result is as below.

For more details you may refer to my sample file.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Order Date column of your Data Table to the Date column of the Clendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and month number.  Sort the Month name by the month number.  Create slicers for Year and Month name and select 2021 and October there.  Write these measures:

Charge = sum(Data[amount])

Charge in same period last year = calculate([charge],sameperiodlastyear(calendar[date]))

Hope this helps.

[AnaStone_100]

My scenario is not that simple 🙂 This  does not work if i want to make monthly comparison by using check in date .I want to use in my table  a diffent date (flight check-in date),not created date(from calendar) and then compare metrics like sales and so on....the calendar date could be only use as  a filter(slicer)

[Ashish_Mathur]

It should work.  Describe your question.  On a sample dataset, show the expected result.

[VahidDM]

Hi @AnaStone_100 

You can use SELECTEDVALUE and SAMEPERIODLASTYEAR  in your measure to find that.

Can you send a sample of your data in table format?

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[AnaStone_100]

Thanks. Sure, but how to send you? Via email?

[VahidDM]

Copy and past your data here.

Appreciate your Kudos!!

[AnaStone_100]

well i can not do that...any other way?