Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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] )
Thanks so much @Anonymous for your help!
@Anonymous thanks for this but with the ytd I still cant make it work. Any more specific ideas?
User | Count |
---|---|
22 | |
14 | |
11 | |
7 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |