How to create a measure based on slicer selection

[Pallavi_m]

Hi,

I have a table that contains these values.. upto Oct 2040(monthyear)

A slicer is created on monthyear column.

-As per the slicer selection, I want to have a  measure that displays Oct 2022 if Oct2022 is selected in the slicer.

-As per the slicer selection, I want to have a  measure that displays Nothing if Dec2022 is selected in the slicer, as data wont be available currently for future years.

- As per the slicer selection, I want to have a  measure that displays Jan 2022 if Jan2022 is selected in the slicer, as data woould be present for the past.

id	index	year	month	monthyear
20221	1	2022	1	Dec 2021
20222	2	2022	2	Jan 2022
20223	3	2022	3	Feb 2022
20224	4	2022	4	Mar 2022
20225	5	2022	5	Apr 2022
20226	6	2022	6	May 2022
20227	7	2022	7	Jun 2022
20228	8	2022	8	Jul 2022
20229	9	2022	9	Aug 2022
202210	10	2022	10	Sep 2022
202211	11	2022	11	Oct 2022
202212	12	2022	12	Nov 2022
20231	13	2023	1	Dec 2022
20232	14	2023	2	Jan 2023

[danextian · Accepted solution]

Hi @Pallavi_m ,

If you create a relationship between your Period and Fact tables, the measure will naturally show blank if the month  selected in  period doesn't exist yet in your fact table. You can create a Month Year calculated colum in Fact, say

Month Year = FORMAT ( Fact[Date], "MMM YYYY" ) and then create relationship between this column and monthyear in Period.

[v-jayw-msft · Accepted solution]

Hi @Pallavi_m ,

Create a measure like below and add it to visual filter set value = 1.

Measure = IF(SELECTEDVALUE('Table'[monthyear]) in VALUES('calendar'[monthyear])&&DATEVALUE(SELECTEDVALUE('Table'[monthyear]))<=EOMONTH(TODAY(),0),1,0)

[danextian · Accepted solution]

Hi @Pallavi_m ,

If you create a relationship between your Period and Fact tables, the measure will naturally show blank if the month  selected in  period doesn't exist yet in your fact table. You can create a Month Year calculated colum in Fact, say

Month Year = FORMAT ( Fact[Date], "MMM YYYY" ) and then create relationship between this column and monthyear in Period.