Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 TotalSalary
04-01-2019 A Peter R 1 2 3 6
04-01-2019 A Mary C 2 3 4 9
04-01-2019 B John R 3 4 5 12
04-01-2019 B James C 4 5 6 14
04-01-2019 C Henry C 5 6 7 18
05-01-2019 A Peter R 2 3 4 9
05-01-2019 A Mary C 3 4 5 12
05-01-2019 B John R 4 5 6 14
05-01-2019 B James C 5 6 7 18
05-01-2019 C Henry C 6 6 6 18

 

Date table information:
https://community.powerbi.com/t5/Desktop/previousmonth-does-not-work/m-p/799066/highlight/false

 

Matrix table
Measure Order
BasicSalary 01
Allowance1 02
Allowance2 03
TotalSalary 04

 

 

I set the measures as below
BasicSalary(Sum) = SUM(Data[BasicSalary])
Allowance1(Sum)=SUM(Data[Allowance1])
Allowance2(Sum)=SUM(Data[Allowance2])
TotalSalary = SUM(Data[TotalSalary])
BasicSalarylastmonth = CALCULATE(SUM(Data[BasicSalary]),PREVIOUSMONTH('date table'[Data[Date]]))
Allowance1lastmonth = CALCULATE(SUM(Data[Allowance1]),PREVIOUSMONTH('date table'[Data[Date]]))
Allowance2lastmonth = CALCULATE(SUM(Data[Allowance2]),PREVIOUSMONTH('date table'[Data[Date]]))
TotalSalarylastmonth = CALCULATE(SUM(Data[TotalSalary]),PREVIOUSMONTH('date table'[Data[Date]]))
BasicSalary(diff) = BasicSalary(Sum) - BasicSalarylastmonth
Allowance1(diff) = Allowance1(Sum) - Allowance1lastmonth
Allowance2(diff) = Allowance2(Sum) - Allowance2lastmonth
TotalSalary(diff) = TotalSalary - TotalSalarylastmonth

 

 

1 ACCEPTED SOLUTION

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:

1.JPG2.JPG

Best Regards,

Lin

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

 

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:

1.JPG2.JPG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi, @v-lili6-msft 

 

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

hi, @Anonymous 

Use this formula to add a calculate column [YearMonth]

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

3.JPG

 

and here is my sample pbix, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

hi @v-lili6-msft 

 

i trired it but it was unsuccessful, beacuse my date table and your date table are different. do u mind use my table to try again? thanks 

hi, @Anonymous 

Just need a YEAR as filter together.

If you don't have YearMonth, you could also put a year filed Year for slicer.

If you still have problem, please share your sample pbix file.

 

Best Regards,

Lin

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.