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 |
Solved! Go to 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.
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)
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)
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.
User | Count |
---|---|
133 | |
82 | |
64 | |
57 | |
55 |
User | Count |
---|---|
212 | |
109 | |
88 | |
82 | |
76 |