Filtering a map based on time period from a slicer

[elliotb]

Hi All,

I have a dataset that contains a column called date. Currently I am plotting this data set on a map based on zip code. I would like to add functionality so that the map only display points where the date falls into the selected range:

i.e when the slicer says:

LTD: Shows all points(Launch to Date)

YTD: Shows all points where date is in current year

MTD: Shows all points where data is in current month

Q1: Shows all points where data is between 1/1/2021-4/1/2020 etc.

Is there a way to do this easily? I was thinking I could create a measure that is a binary that switches to 1 - 0 depending on what the time period selected in the slicer is but I haven't been able to figure it out. I have already experimented with the Timeline 2.4.0 custom visual and while this works this isn't exactly the functionality I am looking for.

[v-stephen-msft · Accepted solution]

Hi @elliotb ,

According to your description, I think you are going to show maps of different periods by filtering LTD, YTD, MTD and Q1.

Here's my solution.

Sample data

1.Create a seperate table by entering data.

2.Create a calendar table.

Date = CALENDAR(MIN('Table (2)'[date]),MAX('Table (2)'[date]))

3.Relationships is as follows.

4.Create a flag measure. Put it into the map visual and set show items when the value is 1.

flag = 
VAR _DATE =
    MAX ( 'Date'[Date] )
VAR _TODAY =
    TODAY ()
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Period] ),
        "LTD", 1,
        "YTD", IF ( YEAR ( _DATE ) = YEAR ( _TODAY ), 1 ),
        "MTD",
            IF ( YEAR ( _DATE ) = YEAR ( _TODAY ) && MONTH ( _DATE ) = MONTH ( _TODAY ), 1 ),
        "Q1",
            IF ( _DATE >= DATE ( 2020, 4, 1 ) && _DATE <= DATE ( 2021, 1, 1 ), 1 )
    )

5.Now you can filter by the slicer.

Best Regards,

Stephen Tao

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

[amitchandak]

@elliotb , with help from a date table 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

LYTD =

var _max = maxx(allselected('Date', Date[Date])

return

CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=_max ))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA