previousmonth does not work

[Anonymous]

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

[Anonymous]

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

[AnkitBI · Accepted solution]

Try Below

lastmonth = CALCULATE(SUM(Data[Salary]),PREVIOUSMONTH(Data[Date]))

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[AnkitBI]

Thanks @Anonymous