Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

Employee Promotions

Hi,    I need help with a query to show employee promotions within a selected month.    I have a date table which is used as a slice to show data for that month. MAX(date) is used.    I have a ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi yaman123 ,

     

    So you can do some steps as follows.

    1. create a "Month" column in table "Date".

    month = MONTH('Date'[Date])

    2. create a measure and drag it into the filter of the table visual. (is 1)

    Measure = 

    var x1=SELECTEDVALUE('Date'[month])

    var x2=MAXX('Table 3',MONTH('Table 3'[FROM DATE]))

    return

    IF(x2>=x1-6&&x2<=x1,1,0)
    Result:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi yaman123 ,

 

So you can do some steps as follows.

1. create a "Month" column in table "Date".

month = MONTH('Date'[Date])

2. create a measure and drag it into the filter of the table visual. (is 1)

Measure = 

var x1=SELECTEDVALUE('Date'[month])

var x2=MAXX('Table 3',MONTH('Table 3'[FROM DATE]))

return

IF(x2>=x1-6&&x2<=x1,1,0)
Result:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago

hI Anonymous 

 

Can this work with Month Year instead of month? I am using a Month Year column in the date table for the rest of the report and would like to keep it consistent throughout? 

 

I have this formula which created the month year column

Month Year = FORMAT([Date],"mmmm")& " "&year([Date])
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi yaman123 ,

     

    I don't think the "month year" column you gave can replace the "month" column. "Month year" column is "Text" type other than "whole number" Type. You'd better split the column and transform the type from "text" into "Whole number". Or you can recreate a month column like above.

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • yaman123's avatar
    yaman123
    Icon for Post Partisan rankPost Partisan
    5 years ago

    Can this work with Max(date,date) because the report is run end of the month?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi yaman123 ,

     

     I did the test according to your request and unfortunately it failed.

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

                                                                                                                                                                                                                                               

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • yaman123's avatar
    yaman123
    Icon for Post Partisan rankPost Partisan
    5 years ago

    No worries. Thanks for your help!