Forum Discussion
Helper IHow to create a measure based on slicer selection
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 |
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.
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)
2 Replies
- danextian
Super User
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.
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)