Forum Discussion
previousmonth does not work
Hi,
I have two data set. first one is data and second is date table
Data:
Date Department Staff Staff type Salary
04-01-2019 A Peter R 1
04-01-2019 A Mary C 2
04-01-2019 B John R 3
04-01-2019 B James C 4
04-01-2019 C Henry C 5
05-01-2019 A Peter R 2
05-01-2019 A Mary C 3
05-01-2019 B John R 4
05-01-2019 B James C 5
05-01-2019 C Henry C 6
06-01-2019 A Peter R 3
06-01-2019 A Mary C 4
06-01-2019 B John R 5
06-01-2019 B James C 6
06-01-2019 C Henry C 7
date table (StartYear:2019, End Year:2039)
let
StartDate = #date(StartYear,1,1),
EndDate = #date(EndYear,12,31),
NumberOfDays = Duration.Days( EndDate - StartDate ),
Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
#"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
#"Inserted Day Name"
Data model:
Connection between date in Data table and FullDateAlternatekey in date table
measure:
lastmonth = CALCULATE(SUM(Data[Salary]),PREVIOUSMONTH('date table'[FullDateAlternateKey]))
then
i create a matrix on Dashboard
Row: Date,Staff,Staff type
Column: Blank
value: Salary, lastmonth
but the lastmonth column is blank on the matrix
does anyone know why lastmonth column is blank? Thanks
- Try Below
lastmonth = CALCULATE(SUM(Data[Salary]),PREVIOUSMONTH(Data[Date]))
Thanks
Ankit JainDo Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
3 Replies
- AnonymousNot applicable
my expect matrix is below
Date Department Staff type Staff Salary lastmonthsalary
04-01-2019 A R Peter 1
04-01-2019 A C Mary 2
04-01-2019 B R John 3
04-01-2019 B C James 4
04-01-2019 C C Henry 5
05-01-2019 A R Peter 2 1
05-01-2019 A C Mary 3 2
05-01-2019 B R John 4 3
05-01-2019 B C James 5 4
05-01-2019 C C Henry 6 5
06-01-2019 A R Peter 3 2
06-01-2019 A C Mary 4 3
06-01-2019 B R John 5 4
06-01-2019 B C James 6 5
06-01-2019 C C Henry 7 6