Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Best Regards,
Lin
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:
Best Regards,
Lin
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |