Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX (PreviousMonth and switch)

Hi, I have three data set. first one is data and second is date table, last one(Matrixtable) is for a matrix Data set: Date Department Staff Staff type BasicSalary Allowance1 Allowance2 TotalS...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi, Anonymous 

    I have test on my side, for your case, please use YearMonth instead of only Month in slicer or add a Year slicer, then it will work.

    For example:

    Best Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Current Month = 
    SWITCH(SELECTEDVALUE(matrix[Order]),
    "01",[Basic Salary(Sum)],
    "02",[Allowance1(Sum)],
    "03",[Allowance2(Sum)],
    "04",[TotalSalary(Sum)],
    BLANK()
    )
Previous Month = 
    SWITCH(SELECTEDVALUE(matrix[Order]),
    "01",[Basic Salary(lastmonth)],
    "02",[Allowance1(lastmonth)],
    "03",[Allowance2(lastmonth)],
    "04",[TotalSalary(lastmonth)],
    BLANK()
    )
Previous Month = 
    SWITCH(SELECTEDVALUE(matrix[Order]),
    "01",[Basic Salary(diff)],
    "02",[Allowance1(diff)],
    "03",[Allowance2(diff)],
    "04",[TotalSalary(diff)],
    BLANK()
    )

 

I want to create a matrix for present the data as below (it is my expection)

                        Current Month      Last Month               Difference(CurrentMonth - LastMonth)

Basic Salary

Allowance1

Allowance2

Total Salary

 

Then

 

I create a matrix and a slicer for select the date on Dashboard

Row: Measure

Column: Blank

Value: current month, last month, difference

 

but now,

when i select May in Date Slicer


                      Current Month        Last Month          Difference( CurrentMonth - LastMonth)

Basic Salary               20                                              20

Allowance1               24                                              24

Allowance2               28                                              28

Total Salary              72                                               72

 

 

does any know why the last month is blank and amounth in different are wrong in Matrix? i check the formula in card. the amounth of formula displayed in card is correct, but it is wrong if i display same formula in Matrix.

 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi, Anonymous 

    I have test on my side, for your case, please use YearMonth instead of only Month in slicer or add a Year slicer, then it will work.

    For example:

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, v-lili6-msft 

       

      may u provide some screenshot that how you create YearMonth column? thanks 

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi, Anonymous 

        Use this formula to add a calculate column [YearMonth]

        YearMonth = YEAR('date table'[Date])*100+MONTH('date table'[Date])

         

        and here is my sample pbix, please try it.

         

        Best Regards,

        Lin