Create two columns together for MTD/YTD use case

[metricwise]

Hello,

I am trying to create two columns - "Dates", "Tag" such that "Dates" contains all the dates that fall in MTD and then the corresponding rows to that should be tagged as "MTD".

I tried using Table.FromList however I don't know how to add create two columns.

When I use Table.FromRecords, I get this error - "We cannot convert the value #date(2020, 8, 1) to type Record"

#"Create Table" = Table.FromList(List.Dates(MonthStart, Duration.Days(Yesterday - MonthStart) + 1, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Dates"})

Please suggest a solution.

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}}),

    Today = DateTime.Date(DateTime.FixedLocalNow()),
    YearStart = Date.Day(#datetime(Date.Year(Today), 1, 1)),
    QuarterStart = Date.StartOfQuarter(Today),
    MonthStart = Date.StartOfMonth(Today),
    WeekStart = Date.StartOfWeek(Today, Day.Monday),
    Yesterday = Date.AddDays(Today, -1),

    #"Create Table" = Table.FromRecords(List.Dates(MonthStart, Duration.Days(Yesterday - MonthStart) + 1, #duration(1, 0, 0, 0))),
in
    #"Create Table"

[Anonymous]

Hi @metricwise ,

Did I answer your question? Please mark my reply as solution, thank you~

[Anonymous]

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

[metricwise]

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.

[Anonymous · Accepted solution]

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

[Ajinkya369]

Hi @metricwise ,

I did some change in your m code to resolve the error which you are getting.

I replaced the function Table.FromRecord with Table.FromValue

Here is the output of your M code:

Output

Modified M-Code:

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}}),

    Today = DateTime.Date(DateTime.FixedLocalNow()),
    YearStart = Date.Day(#datetime(Date.Year(Today), 1, 1)),
    QuarterStart = Date.StartOfQuarter(Today),
    MonthStart = Date.StartOfMonth(Today),
    WeekStart = Date.StartOfWeek(Today, Day.Monday),
    Yesterday = Date.AddDays(Today, -1),

    #"Create Table" = Table.FromValue(List.Dates(MonthStart, Duration.Days(Yesterday - MonthStart) + 1, #duration(1, 0, 0, 0)))
in
    #"Create Table"

If your problem is solved then accept this reply as a solution.

Thank you

Ajinkya

Master Data Analysis - 

[Ajinkya369]

Hi @metricwise ,

I did some change in your m query to resolve the error you are getting.

I replaced the function Table.FromRecord with Table.FromValue.

Here is the output image  after performing the changes:

Output

Working M Code:

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}}),

    Today = DateTime.Date(DateTime.FixedLocalNow()),
    YearStart = Date.Day(#datetime(Date.Year(Today), 1, 1)),
    QuarterStart = Date.StartOfQuarter(Today),
    MonthStart = Date.StartOfMonth(Today),
    WeekStart = Date.StartOfWeek(Today, Day.Monday),
    Yesterday = Date.AddDays(Today, -1),

    #"Create Table" = Table.FromValue(List.Dates(MonthStart, Duration.Days(Yesterday - MonthStart) + 1, #duration(1, 0, 0, 0)))
in
    #"Create Table"

If your problem is solved then please accept this as a solution

Thank you

Ajinkya

Master Data Analysis

[metricwise]

Hi @Ajinkya369 ,

Thanks for the quick fix. However, I was wondering how can I add another column Column2 "Tag" which will store value as "MTD" against each of these Column1 "Dates" values? 

So, the output would be like below:

Dates	Tag
8/1/2020	MTD
8/2/2020	MTD
8/3/2020	MTD
8/4/2020	MTD
8/5/2020	MTD
8/6/2020	MTD

After this, I would apply the same logic to generate YTD, QTD values.

[amitchandak]

@metricwise , there is code here to generate a calendar using M.

see if that can help

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns