Filter/Measure data between dates in slicer

[ubv]

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 =

VAR SelectedPeriod = SELECTEDVALUE('Year'[Year])&"-"&SELECTEDVALUE('Month'[Month])

RETURN IF(SelectedPeriod >= MIN('Table'[From]) && SelectedPeriod <= MAX('Table'[Until]);1;0)

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,

[ubv]

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 =

VAR SelectedPeriod = SELECTEDVALUE('Year'[Year])&"-"&SELECTEDVALUE('Month'[Month])

RETURN IF(SelectedPeriod >= MIN('Table'[From]) && SelectedPeriod <= MAX('Table'[Until]);1;0)

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,

[v-piga-msft]

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

[ubv]

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,

[helloamalexpark]

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