Forum Discussion
Create two columns together for MTD/YTD use case
Hi metricwise ,
You could Add a Custom column using like this:
The full code in Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDLCcAwDAPQXXIuyHL+s4Tsv0ZTSrF6fMgG2WulDsLNLe1rpQEPNGRNiqIGKpomPVAwNJmBDFqIoHRwUEpM8GnB+U4aWIRnswYH2EIN7DIKjtA5euoFbtrN/x9xberfT/YN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "QTD", each if Date.IsInCurrentQuarter([Value]) then "QTD" else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "YTD", each if Date.IsInCurrentYear([Value]) then "YTD" else null), #"Added Custom" = Table.AddColumn(#"Added Custom2", "MTD", each if Date.IsInCurrentMonth([Value]) then "MTD" else null), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Value", Order.Descending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Value", "MTD", "QTD", "YTD"}) in #"Reordered Columns"Then the final table will look like this:
Best regards,
Eyelyn Qin
Hi metricwise ,
According to my understanding ,you want to create Date column with a Tag column to specify the MTD/QTD/YTD ,right?
You could use the following formula:
DateTable =
CALENDAR ( "2019/11/1", TODAY () )latestDateColumn =
CALCULATE ( MAX ( DateTable[Date] ), ALL ( DateTable ) )monthDiff =
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
MONTH
) = 0,
"MTD"
)quarterDiff =
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
QUARTER
) = 0,
"QTD"
)yearDiff =
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
YEAR
) = 0,
"YTD"
)Tag =
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
MONTH
) = 0,
"MTD",
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
QUARTER
) = 0,
"QTD",
IF (
DATEDIFF (
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( DateTable[latestDateColumn] ),
YEAR
) = 0,
"YTD"
)
)
)My visualization looks like this:
Is the result what you want? If you have any questions, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
HI Anonymous ,
Thank you for your reply. I see that you have written a DAX Query for this however I want to implement it in M for faster execution.
Hi metricwise ,
You could Add a Custom column using like this:
The full code in Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDLCcAwDAPQXXIuyHL+s4Tsv0ZTSrF6fMgG2WulDsLNLe1rpQEPNGRNiqIGKpomPVAwNJmBDFqIoHRwUEpM8GnB+U4aWIRnswYH2EIN7DIKjtA5euoFbtrN/x9xberfT/YN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "QTD", each if Date.IsInCurrentQuarter([Value]) then "QTD" else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "YTD", each if Date.IsInCurrentYear([Value]) then "YTD" else null), #"Added Custom" = Table.AddColumn(#"Added Custom2", "MTD", each if Date.IsInCurrentMonth([Value]) then "MTD" else null), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Value", Order.Descending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Value", "MTD", "QTD", "YTD"}) in #"Reordered Columns"Then the final table will look like this:
Best regards,
Eyelyn Qin