Forum Discussion
Filter/Measure data between dates in slicer
Hi,
I have a table with the bonuses for each employee. Something like:
From To Monthly Amount
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-01-01 2018-08-31 $1000
Employee B 2018-09-01 2018-12-01 $800
Employee C 2018-01-01 2018-08-31 $200
Employee C 2018-11-01 2018-12-31 $500
I have a slicer, so that the user selects a year and a month, and only the rows affected are shown. For example, if they choose 2018 and 09:
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-09-01 2018-12-01 $800
I managed to do so with a Measure:
In_period =
What I need to do now (and cannot find a way to do so) is the following:
SelectedMonth PreviousMonth
Employee A 700 700
Employee B 800 1000
Employee C 0 200
I tried creating 2 tables, one for each month, with something like FILTER('Table';'Table'[In_period]=1), but it seems I cannot do FILTER and MEASUREs at the same time.
Can you help me with that?
Regards,
3 Replies
Hi ubv,
I'm a little confused about your logic and your scenario.
Do you have another date table? If you have, how do you create the relationship?
What's the logic of your slicer?
I also cannot understand that the value of Previous month, could you describe your requirement in more details so that I can understand it better and get the solution?
Best Regards,
Cherry
Hi v-piga-msft,
Thanks for your reply.
There are 3 tables in total:
- Table: with the actual data (employee, from, to, amount)
- Year: just one column, with years (2016, 2017, 2018)
- Month: just one column, with 12 months (01,02,03...12)
I used the logic in HERE: 2 disconnected tables for the slicer and use a calculated measure as a filter for the visual.
Using the example in the original post. Let's say the user selects 2018 and 09. For September the rows selected should be:
From To Monthly Amount
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-09-01 2018-12-01 $800
For August, the previous month, should be:
From To Monthly Amount
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-01-01 2018-08-31 $1000
Employee C 2018-01-01 2018-08-31 $200
And what I need to do is: group the amounts by employee and show the selected month amounts in one column and the previous ones in another one:
SelectedMonth PreviousMonth
Employee A 700 700
Employee B 800 1000
Employee C 0 200
The problem I have is that I don't know how to manage different dates in each column, the SelectedMonth coming from a slicer.
Thank you,
Using the example in the original post. Let's say the user KissAnime selects 2018 and 09. For September the rows 3ds emulator selected should be:
From To Monthly Amount
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-09-01 2018-12-01 $800
For August, panda helper the previous month, should be:
From To Monthly Amount
Employee A 2018-01-01 2018-12-01 $500
Employee A 2018-01-01 2018-12-01 $200
Employee B 2018-01-01 2018-08-31 $1000
Employee C 2018-01-01 2018-08-31 $200
