Add Custom column based on text in another column (if Quarter Column "2022-Q1" "2022-Q2")

[Anonymous]

Hi all!

I am just getting started with Power Query and "Get and transform" in general but have some previous understanding of excel functions/formulas and not with DAX and even M language when creating a custom column.

My goal is be able to add a column by Q1,Q2,Q3 and Q4 with each row amount (as shown below). I tried doing in add column with a formula as shown below, but I'm hitting a deadend. Truly will appreciate if someone can please help! I understand PBI cannot add create a custome column using a string text and value column. Can you please help with the synthax of creating a column by quarter adding each row value. 

Thank you so mcuh! 

[BA_Pete]

Hi @Anonymous ,

The basic answer is to select your [Quarter] column, go to the Transform tab > Pivot, and choose [Amount] as your aggregation value.

The further answer is a question: Why would you want to do this? You're moving your data away from a more normalised structure, thus making it size-inefficient, and also potentially causing yourself headaches in the future when you come to write measures.

Pete

Pete

[Anonymous]

Thank you Pete, I see your point. How would I then write a DAX calculation not changing the current structure when Quarter colum is a text and amount is value?  The end goal is to calculate Current YR 2022-Q1 - 2022-Q2 and thus calculate Current Yr 2022-Q1 - Prior Yr 2021-Q1? 

[BA_Pete]

First, create a proper date in your Fact table. In Power Query, you can create a new custom column like this:

#date([Year], [Period], 01)

Then create a calendar table. I've added the M code for a simple calendar at the bottom of this post to help out. I'd recommend running this in a Dataflow each night so it's available to all of your future reports. I'm going to assume you're using my calendar for the measures below.

Apply this to the model, go to the Fields list, click the ellipsis next to your calendar table and choose 'Mark as date table'.

Now relate your Calendar table to your Fact table on Calendar[Date] ONE : MANY Fact[Date].

Now, for your prior year measure, it would be something like this:

_amountPY =
CALCULATE(
    SUM(FactTable[Amount]),
    SAMEPERIODLASTYEAR(Calendar[date])
)

This is a generic PY measure, so will always give you the prior year's value for whatever calendar period you select, so will work fine for quarters too.

Your prior quarter measure would be something like this:

_amountPQ =
VAR __currQ = MAX(Calendar[relativeQuarter])
RETURN
CALCULATE(
    SUM(FactTable[Amount]),
    Calendar[relativeQuarter] = __currQ - 1
)

Now you have prior year and prior quarter, you should be able to easily create YoY, QoQ etc.

Just make sure that whenever you visualise anything against time, you use the relevant column from the Calendar table against the measure, not from the Fact table.

Simple calendar table:

let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  // Build calendar
  Source = { Number.From(#date(2015,1,1))..Number.From(#date(2023,12,31)) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
    addYear = Table.AddColumn(renCols, "year", each Date.Year([date])),
    addRelativeYear = Table.AddColumn(addYear, "relativeYear", each [year] - Date.Year(Date.Today)),
    addQuarterKey = Table.AddColumn(addRelativeYear, "quarterKey", each Date.QuarterOfYear([date])),
    addRelativeQuarter = Table.AddColumn(addQuarterKey, "relativeQuarter", each ([year] * 4 + [quarterKey]) - (Date.Year(Date.Today) * 4 + Date.QuarterOfYear(Date.Today))),
    addMonthKey = Table.AddColumn(addRelativeQuarter, "monthKey", each Date.Month([date])),
  addMonth = Table.AddColumn(addMonthKey, "month", each Text.Start(Date.MonthName([date]), 3)),
  addMonthYear = Table.AddColumn(addMonth, "monthYear", each Text.Combine({[month], Text.End(Text.From(Date.Year
([date])),2)}, " ")),
  addRelativeMonth = Table.AddColumn(addMonthYear, "relativeMonth", each (Date.Year([date]) * 12 + [monthKey]) - (Date.Year(Date.Today) * 12 + Date.Month(Date.Today))),
  addDayKey = Table.AddColumn(addRelativeMonth, "dayKey", each Date.DayOfWeek([date])),
  addDay = Table.AddColumn(addDayKey, "day", each Text.Start(Date.DayOfWeekName([date]), 3)),
  addRelativeDay = Table.AddColumn(addDay, "relativeDay", each [date] - Date.Today),
    chgTypes = Table.TransformColumnTypes(addRelativeDay,{{"year", Int64.Type}, {"relativeYear", Int64.Type}, {"quarterKey", Int64.Type}, {"relativeQuarter", Int64.Type}, {"monthKey", Int64.Type}, {"relativeMonth", Int64.Type}, {"dayKey", Int64.Type}, {"relativeDay", Int64.Type}, {"month", type text}, {"monthYear", type text}, {"day", type text}})
in
    chgTypes

Pete

[BA_Pete]

I've just found another post I did a couple of years ago looking at calendar table best practice in a bit more detail if it helps:

https://community.powerbi.com/t5/Desktop/Count-rows-based-on-a-filter-date/m-p/1518433#M625777 

Pete