measure calculations previous month value in matrix visual

[TechR21]

Hi,

I have the following simple matrix in powerbi

total costs are calculated by a sum of a column in my table

total costs = SUM('table1'[Costs])

What i try to do with the measure "total costs last month" is to get the value of the previous month. I want to use this value then to calculate some things.

The measure is :

total costs last month =

calculate(

    [total costs],

    PARALLELPERIOD('Date'[Date],-1, MONTH)

I created a date table to use for this measure, and to join with my table1.

Because table1 has only month dates in it, Its joined as many to many with date table.

As you can see in the matrix, I only get a value in the total column. How do I get the right value in here?

What i would want is:

Month	total costs	total costs last month
May	151,042.66
June	154,228.14	151,042.66
July	167,772.21	154,228.14

[JoeBarry]

Sure, Open a Blank query and copy this in the advanced editor.

let
  // configurations start
  Today = Date.From(DateTime.LocalNow()),
  // today's date
  FromYear = Date.Year(DateTime.LocalNow()) -3,
  // set the start year of the date dimension. dates start from 1st of January of this year
  ToYear = Date.Year(DateTime.LocalNow()) +6,
  // set the end year of the date dimension. dates end at 31st of December of this year
  StartofFiscalYear = 7,
  // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
  firstDayofWeek = Day.Monday,
  // set the week's start day, values: Day.Monday, Day, Sunday....
  // configuration end
  FromDate = #date(FromYear,1,1),
  ToDate = #date(ToYear,12,31),
  Source = List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
  #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
  #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
  #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
  #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
  #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
  #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
  #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
  #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
  #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
  #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
  #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
  #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
  #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
  #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
  #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
  #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date], firstDayofWeek), Int64.Type),
  #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date], firstDayofWeek), Int64.Type),
  #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date], firstDayofWeek), type date),
  #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date], firstDayofWeek), type date),
  FiscalMonthBaseIndex = 13-StartofFiscalYear,
  adjustedFiscalMonthBaseIndex = if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
  #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date], adjustedFiscalMonthBaseIndex)),
  #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"FiscalBaseDate", type date}}),
  #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
  #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1", {{"Year.1", "Fiscal Year"}}),
  #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
  #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1", {{"Quarter.1", "Fiscal Quarter"}}),
  #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
  #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1", {{"Month.1", "Fiscal Month"}}),
  #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3", {"FiscalBaseDate"}),
  #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date] - Today, type duration),
  #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
  #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days", {{"Age", "Day Offset"}}),
  #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
  #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1", {{"Month Offset", Int64.Type}}),
  #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
  #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2", {{"Year Offset", Int64.Type}}),
  #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
  #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3", {{"Quarter Offset", Int64.Type}}),
  #"Added custom" = Table.AddColumn(#"Changed Type4", "Datekey", each Date.ToText(Date.From([Date]), "yyyyMMdd")),
  #"Changed column type" = Table.TransformColumnTypes(#"Added custom", {{"Datekey", Int64.Type}}),
  #"Added custom 1" = Table.AddColumn(#"Changed column type", "Month-Text", each Text.PadStart( Text.From([Month]), 2, "0")),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Added custom 1", {{"Month-Text", type text}}),
  #"Inserted merged column" = Table.AddColumn(#"Changed column type 1", "Month-Year", each Text.Combine({[#"Month-Text"], Text.From([Year])}, "-"), type text),
  #"Extracted first characters" = Table.TransformColumns(#"Inserted merged column", {{"Month Name", each Text.Start(_, 3), type text}}),
  #"Extracted first characters 1" = Table.TransformColumns(#"Extracted first characters", {{"Day Name", each Text.Start(_, 3), type text}})
in
  #"Extracted first characters 1"

After loading into your report. Make sure to mark as a date table by clicking on the 3 dots beside the table name table name 

And select th Date column. You can also set the Month Name column to be sorted By the Month column and the Day Name column to be sorted by the day of the week column.

[TechR21]

this finally works. i think something went wrong with the date table. Thanks!

[JoeBarry · Accepted solution]

Hi @TechR21 

If you are using a date table in your dataset, please try 

Costs Previous Month = Calculate([total costs], DATEADD('DImDate'[Date], -1, MONTH))

 Thanks

Joe

If this post helps, then please  Accept it as the solution 

[TechR21]

I tried this (and a few others already) but doesnt work :

[JoeBarry]

My solution works, but if you are using a Date Table https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

[TechR21]

yes you are right.I made some changes but not correct value now.

the problem is i think the relationship between the two tables. Its not a 1-1 relationship because table1 has multiple rows with the same date.

[JoeBarry]

Hi @TechR21 

You can download my PBIX here https://drive.google.com/file/d/1Y4A_u7kyP7ejJwoOQeTvmcaZjByoXQMp/view?usp=sharing

Usally the table with the costs would have multiple duplicate dates in it. Then you would have a one to many from the date table to the cost table. 

In my PBIX, I vreated a Bi Directional relationship and changed it to Date filters Costs. In your visual you would use the date from the Date table and not the Cost table

Thanks

Joe

If this post helps, then please Accept it as the solution 

[TechR21]

Even with the date column from date table it still not working..

there might be something wrong with the date table but thats pretty straight forward so i dont understand what Im missing.

Do you have a script for me so I can create a date table just like yours?