allexcept ytd measure

[userdata]

Hi all,

I tried to do a ytd of a measure with allexcept but it doesnt give me the right values. I have some data in this link: https://github.com/userdata21/test/blob/main/test.pbix

I am using this measure 

Measure = CALCULATE(SUM('Table'[Yearly Budget])/12, ALLEXCEPT('Table','Table'[Costs],'Table'[Department]))  in order to get the monthly budget as I have a yearly budget table where the value is only written for the whole year in the december date column. Therefore i divide this by 12 and use allexcept so when i filter for department it still slices correctly.The problem is I have to do a YTD of this measure but when i add datesytd (date(date)) it wont show the right values. How can I achieve that referencing the measure?

[Anonymous]

You have to change the model a bit to make this easier. Here's what I think it should be. It's important that some fields are hidden for the model to work correctly. They should not be exposed to the end user.

Here's the M code that creates all the tables in PQ:

// Fact
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZExC4MwEEb/S2bhctFoMrp16NRVHIoUKRS7tP+/H41Q09wFHmZ5XOK9aTKWCTjr2DTmfFuvD5zgdMGHnZkbOI6A5rTJaQkozj6mI6Ao+xRPQFG6pPQE6hcNBLT3JiUQUBQ/JCcSUJz4VdgSUJSQFCZWF+z75DjiYsHOxjD8iVmtcVme7+1139Y0cRx/GzgWEz2hmuh1ZTrR82U/0evLiKIXypKiF8ucomfLpJX3ZV0r/5vFrewvC5x5jMrhEGX+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Department = _t, #"Yearly Budget" = _t, Costs = _t, #"other costs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Department", type text}, {"Yearly Budget", Int64.Type}, {"Costs", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Costs", "Cost Center"}, {"other costs", "Other Costs"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Cost Center"}, #"Cost Center", {"Cost Center"}, "Cost Center.1", JoinKind.Inner),
    #"Expanded Cost Center.1" = Table.ExpandTableColumn(#"Merged Queries", "Cost Center.1", {"_CostCenterID"}, {"_CostCenterID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Cost Center.1",{"Cost Center"}),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Columns", {"Department"}, Department, {"Department"}, "Department.1", JoinKind.Inner),
    #"Expanded Department.1" = Table.ExpandTableColumn(#"Merged Queries1", "Department.1", {"_DepartmentID"}, {"_DepartmentID"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Department.1",{"Department"}),
    #"Inserted Start of Month" = Table.AddColumn(#"Removed Columns1", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Start of Month",{"Date"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Start of Month", "Month"}}),
    #"Filled Up" = Table.FillUp(#"Renamed Columns1",{"Yearly Budget"}),
    #"Divided Column" = Table.TransformColumns(#"Filled Up", {{"Yearly Budget", each _ / 12, type number}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Divided Column",{{"Yearly Budget", "MonthlyBudget"}, {"Other Costs", "OtherCosts"}, {"Month", "_FirstDayOfMonth"}})
in
    #"Renamed Columns2"

// Cost Center
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZExC4MwEEb/S2bhctFoMrp16NRVHIoUKRS7tP+/H41Q09wFHmZ5XOK9aTKWCTjr2DTmfFuvD5zgdMGHnZkbOI6A5rTJaQkozj6mI6Ao+xRPQFG6pPQE6hcNBLT3JiUQUBQ/JCcSUJz4VdgSUJSQFCZWF+z75DjiYsHOxjD8iVmtcVme7+1139Y0cRx/GzgWEz2hmuh1ZTrR82U/0evLiKIXypKiF8ucomfLpJX3ZV0r/5vFrewvC5x5jMrhEGX+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Department = _t, #"Yearly Budget" = _t, Costs = _t, #"other costs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Department", type text}, {"Yearly Budget", Int64.Type}, {"Costs", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Costs"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Costs", "Cost Center"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "_CostCenterID"}})
in
    #"Renamed Columns1"

// Department
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZExC4MwEEb/S2bhctFoMrp16NRVHIoUKRS7tP+/H41Q09wFHmZ5XOK9aTKWCTjr2DTmfFuvD5zgdMGHnZkbOI6A5rTJaQkozj6mI6Ao+xRPQFG6pPQE6hcNBLT3JiUQUBQ/JCcSUJz4VdgSUJSQFCZWF+z75DjiYsHOxjD8iVmtcVme7+1139Y0cRx/GzgWEz2hmuh1ZTrR82U/0evLiKIXypKiF8ucomfLpJX3ZV0r/5vFrewvC5x5jMrhEGX+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Department = _t, #"Yearly Budget" = _t, Costs = _t, #"other costs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Department", type text}, {"Yearly Budget", Int64.Type}, {"Costs", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Department"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "_DepartmentID"}})
in
    #"Renamed Columns"

// Measures_
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"})
in
    #"Removed Columns"

and the measures:

MEASURE Measures_[Budget Allocation YTD] = 
CALCULATE(
    [Budget Allocation],
    DATESYTD( 'Calendar'[Date] )
)

MEASURE Measures_[Budget Allocation] =
SUM( 'Fact'[MonthlyBudget] )

[userdata]

Thanks so much @Anonymous  for your help!

[userdata]

@Anonymous thanks for this but with the ytd I still cant make it work. Any more specific ideas?

[Anonymous]

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI