cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
marklam Frequent Visitor
Frequent Visitor

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
AnkitBI Established Member
Established Member

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.

3 REPLIES 3
marklam Frequent Visitor
Frequent Visitor

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

AnkitBI Established Member
Established Member

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.

AnkitBI Established Member
Established Member

Re: previousmonth does not work

Thanks @marklam 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 64 members 1,361 guests
Please welcome our newest community members: