Forum Discussion
Parameters based on measures in custom table?
Thanks for the quick responses Mariusz and Geradav.
It is looking more and more like the business ask I am working on requires some type of recursive calculation. In my brief research, it seems like DAX doesnt like to do recursion so I am left only with M.
If I cant call DAX directly, here is the only other workaround I can think of. Maybe you can help me out. If not, I can post a separate question:
I have normal date and sales tables.
I have a disconnected Date Table that I made in M:
let
Dates = List.Zip({List.Dates(#date(2019,1,1),365,#duration(1,0,0,0))}),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"
And I have another table called "Dynamic Dates" that directly references the above table ("Dates"):
let
Source = List.Dates(List.Min(Dates[Date]),
Duration.Days(List.Max(Dates[Date])-List.Min(Dates[Date])),
#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"
I have two measures built in to my report.
Start Date = MIN(DynamicDates[Date])
End Date = MAX(DynamicDates[Date])
Using those measures, I can use a Date Slicer Visual on my report to dynamically change my calculations. (see photos below)
I want to re-add the Extra to the remaining dates. Is it possible, using M, to Generate a table that is only the Dates from Max(Dynamic Dates) through the end of the year? Then I was thinking that a Calculated Column with Generate List might provide me with my solution...
Thoughts?