How to calculate fiscal year YTD, QTD and MTD without having dates ?

[AishwariyaV]

Hi,

First of all, my dataset is in year and month level. I dont have any dates.

For calculating YTD, MTD and QTD, i have taken the first date of all months.

Second thing is, my data is based on fiscal year starting from october.

I have used ,

Current YTM = TOTALYTD(SUM(Append1[sales]),Append1[Custom])

Previous ytm = CALCULATE([Current YTM],SAMEPERIODLASTYEAR(Append1[Custom]))

(*Note : here custom is date)

My QTD and MTD almost works fine.

But my YTD is taking January as first month instead of october.

Can anyone help me out?

Thanks in advance.

[Anonymous · Accepted solution]

@AishwariyaV 

You need to make slight changes in data model.

1) Create a date column from year and month

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddRLjsMwCADQu2TdBf/PLWZf9f7XmODWmk4gCyuRnzBghTyfxw8ej4MA43xYLWY+Xo8OLh4jEGjOwNDBzyXiNAJF2AxkMoKKzmDun6PokpxDG9TCQJ9BeAYDhxkUeARS7MlzdY693Fzl+hzhuq/kCxAqJI1uxDNncdn5+SLKLl3wHUOzmAvPggiDVDXIeSMK+wo+Uu3XYWLBI5DyDWTgG+QPKgf77v8CZPuaL+C6q/qCSqdmPMK5TyOwQz9K1qDw5wPT/4AoNAIJeIN61RQcwVNvIoS4Qc21IMsIZtFzrH+HczZYcw0SI2iYjuCWvao1cxS9jwIW7+UuIDqTv34B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t, Month = _t, Amount = _t]),
    Date = Table.AddColumn(Source, "Date", each #date(Number.From([Year]),Number.From([Month]),1)),
    ChangedType = Table.TransformColumnTypes(Date,{{"Product", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Amount", Int64.Type}, {"Date", type date}})
in
    ChangedType

2) Use below measure to calculate YTD values

Measure = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]>=DATE(2018,10,1) && 'Table'[Date]<=TODAY()))

3) Similarly you can create other measures

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.