Forum Discussion
Generate datetabel based on 2 slicers
Hi all,
i'm trying to generate a datetable based on 2 slicers, but i seem to be missing something. Somehow the new table returns all the dates starting from 1899. I created measures for RapportageJaar and RapportageMaand and they both show the correct value if i select a date. So why does it show 1899 😞
My code so far:
PeriodeTabel =
// get year and month based on selected filter
VAR RapportageJaar = SELECTEDVALUE('00 Datumtabel'[Jaar])
VAR RapportageMaand = SELECTEDVALUE('00 Datumtabel'[Maand])
// Make start & enddate based on slicer over period of 2 years
VAR StartDatum = DATE([RapportageJaar]-1, [RapportageMaand],1)
VAR EindDatum = DATE([RapportageJaar]+1, [RapportageMaand],1)
// only return yearmonth from dateperiod
RETURN
SUMMARIZE(
ADDCOLUMNS(CALENDAR(StartDatum,EindDatum)
,"Jaarmaand"
,YEAR([Date]) & " " & IF(MONTH([Date]) < 10, "0" & MONTH([Date]), MONTH([Date]))
)
,[Jaarmaand]
)Tnx in advance
Hi Anonymous
is the Earliest Startdatum= your selected date(2021/5) - 12 months = 2020/5, and Earliest Einddatum= your selected date(2021/5) + 11 months = 2022/4, right?
if yes, one quick example bellow. in the example, there are 2 tables, Datatable 1 & Datatable 2.In slicer, it's Datatable 2.
then, create the measure,
Check = VAR _sel = DATE ( SELECTEDVALUE ( 'Datatable 2'[Year] ), SELECTEDVALUE ( 'Datatable 2'[Month] ), 1 ) // I use 2 slicers year&month, so I need to use function Date() to get a whole date. if you use only one slicer Date, you can get the whole date directly by function selectedvalue(). VAR _EarliestEinddatum = EDATE ( _sel, 12 ) //return 2022/5/1 when selected date=2021/5 VAR _EarliestStartdatum = EDATE ( _sel, -12 ) //return 2020/5/1 when selected date=2021/5 RETURN IF ( MIN ( 'Datatable 1'[orderdate] ) < _EarliestEinddatum && MIN ( 'Datatable 1'[orderdate] ) >= _EarliestStartdatum, 1, 0 )Could you please mark the solution you find helpful by clicking Accept as Solution. Really appreciate!😃
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
5 Replies
- Fowmy
Super User
Anonymous
Power BI tables are cannot see the slicer selections in the report. When you create a table and it gets added to your model, report activities cannot affect.
The table that you need to for the calculation can be created and used within the measure as a virtual table instead. - HotChilli
Community Champion
You can't create a calculated table that reacts to slicers in this way (well, you can create it but it won't do what you want). Calculated tables are created at startup.
So the SELECTEDVALUE variables will have values in them but they won't be from a slicer selection.
You can create a table in memory (as part of a measure) that reacts to slicers
Hi HotChilli
can you explain to me how to do that? I'm trying to get this to work. The "Check" column should return a 1 if the yearmonth is between the -12 and + 11 months. I'm kinda stuk in the thinking process now haha
v-xiaotangCommunity Support
Hi Anonymous
is the Earliest Startdatum= your selected date(2021/5) - 12 months = 2020/5, and Earliest Einddatum= your selected date(2021/5) + 11 months = 2022/4, right?
if yes, one quick example bellow. in the example, there are 2 tables, Datatable 1 & Datatable 2.In slicer, it's Datatable 2.
then, create the measure,
Check = VAR _sel = DATE ( SELECTEDVALUE ( 'Datatable 2'[Year] ), SELECTEDVALUE ( 'Datatable 2'[Month] ), 1 ) // I use 2 slicers year&month, so I need to use function Date() to get a whole date. if you use only one slicer Date, you can get the whole date directly by function selectedvalue(). VAR _EarliestEinddatum = EDATE ( _sel, 12 ) //return 2022/5/1 when selected date=2021/5 VAR _EarliestStartdatum = EDATE ( _sel, -12 ) //return 2020/5/1 when selected date=2021/5 RETURN IF ( MIN ( 'Datatable 1'[orderdate] ) < _EarliestEinddatum && MIN ( 'Datatable 1'[orderdate] ) >= _EarliestStartdatum, 1, 0 )Could you please mark the solution you find helpful by clicking Accept as Solution. Really appreciate!😃
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
v-xiaotang This is exactly what i was looking for. You are awesome!!! Tnx
