Calculate PYTD sales for previous months and current month in Power BI measure

[Anonymous]

Hi Community Members,

The below excel table(Sample PY), contains 5 columns, i.e. Category, PY, PYTD, Month, and Date factor(Month +Year). So here I want to create a measure in Power bi that calculate the PYTD value until the current month and this should be dynamic.

Let's say this month is August, as the financial year starts in May month and ends in April, so

for Month May-2022- Py-10, PYTD-10

for month April2022- PY- 20, PYTD- 30 (PYTD-May+ PY-April)

for Month June 2022- Py-40, PYTD-70 (PYTD-April+ PY-June)

for the month of July 2022- PY- 12, PYTD- 82 (PYTD-June+ PY-July)

For the month of August 2022 PY- 16 PYTD-98 (PYTD-July+ PY-August)

Here, what I would like to do is, When the current month comes, the PYTD value will update dynamically up to the current month. Can anyone help me out that how I can do this thing using Power BI DAX? Thanks in Advance!!

Sample PY excel sheet 

@PowerBI @PowerBIDax

[lbendlin]

First thing would be to modify your source data so it has a usable date column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc9BDsIgEIXhu7DuwlIwsMSKJo1tiTompun9ryF0TObBZhZfpkP/bVPprjo1h2+e/Unt3V8mWvLUlTzyNCCByuy1yCumImeRdXw3d5b1U3acyDWOzeUplNcdyC1e8vQgc3g2r4dURJtDCLucUBVGEjaAcJgD4TBjRTgMdzhsgB0OsyAcZuGrKowkzGv4nyPM5zv7Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Month = _t, PY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Month", type text}, {"PY", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each  if List.Contains({"MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},[Month]) then "1 " & [Month] & " 2021" else "1 " & [Month] & " 2022"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Category", "PY", "Date"})
in
    #"Removed Other Columns"

Then you would add a calendar table that identifies your fiscal years.

Dates = ADDCOLUMNS(CALENDAR("2021-05-01","2022-04-30"),"MonthNo",year([Date])*100+month([Date]),"Month",FORMAT([Date],"mmmm yyyy"),"FY",FORMAT(EDATE([Date],-4),"F\YYYYY"),"FQ" ,FORMAT(EDATE([Date],-4),"F\Yyyyy\Qq"))

After that you can use the standard measures to calculate the cumulative sum as needed. You can even use the Quick Measure if you want

PY YTD = TOTALYTD(SUM('Table'[PY]), 'Dates'[Date],ALL(Dates),"04/30")

see attached