cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: previousmonth does not work

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Re: previousmonth does not work

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

Super User I
Super User I

Re: previousmonth does not work

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.

View solution in original post

Highlighted
Super User I
Super User I

Re: previousmonth does not work

Thanks @Anonymous 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors