Knowledge Corner

Stepwise plan (mixed chart: Actual bars + Target line)

Step 0 — Decide the month key (do this first)

Use a MonthStartDate key. It avoids sorting issues and makes relationships/measures stable.

1) Create the month dimension in your existing Calendar

In your Calendar table add:

MonthStart =

DATE(YEAR('Calendar'[Date]), MONTH('Calendar'[Date]), 1)

YearMonth =

FORMAT('Calendar'[Date], "YYYY-MM")

In Model view:

• Set Calendar[YearMonth] sort by Calendar[MonthStart] (or MonthNo if you have it).
  
  

• You will use Calendar[YearMonth] on the chart axis.
  
  

2) Create PTAStaticMonthlyTargets table

MonthNo =

SWITCH(

    PTAStaticMonthlyTargets[Month2026],

    "January", 1,

    "February", 2,

    "March", 3,

    "April", 4,

    "May", 5,

    "June", 6,

    "July", 7,

    "August", 8,

    "September", 9,

    "October", 10,

    "November", 11,

    "December", 12,

    BLANK()

)

You said you have: Month | Target | TYPE [PTA].

Option A (best): If Month is a real date or can be parsed to a date

Create a calculated table (or in Power Query) with MonthStartDate:

PTAStaticMonthlyTargets =

SELECTCOLUMNS(

    'YourSourceTable',

    "MonthStart", DATE(YEAR([Month]), MONTH([Month]), 1),

    "YearMonth", FORMAT(DATE(YEAR([Month]), MONTH([Month]), 1), "YYYY-MM"),

    "Target", [Target],

    "Type", [TYPE]

)

Then filter Type in measures (so future types can be added without breaking).

Option B: If Month is text like “Jan-2026” or “2026-01”

Make a MonthStart from text. Example if Month is YYYY-MM:

MonthStart =

DATE(VALUE(LEFT([Month],4)), VALUE(RIGHT([Month],2)), 1)

Then YearMonth is FORMAT([MonthStart], "YYYY-MM").

Hard rule: Your target table must have one row per month per type (or you must decide SUM vs MAX).

3) Keep targets table disconnected (recommended)

Do not create a relationship if you already have a Calendar→SIOP active relationship and the model is sensitive.

No relationship needed if you use TREATAS in the measure.

4) Create Actual measure (bars)

Pick correct counting logic.

If each SIOP row is one release:

Actual Releases :=

COUNTROWS(SIOP)

If releases repeat per issue and you want unique:

Actual Releases :=

DISTINCTCOUNT(SIOP[Issue key])   -- or your release id

This will count per month automatically when axis is Calendar.

5) Create Target measure (line) using TREATAS

This makes the target respond to the month on the axis without relationships:

Monthly Target (PTA) :=

VAR ym = VALUES('Calendar'[YearMonth])

RETURN

CALCULATE(

    MAX(PTAStaticMonthlyTargets[Target]),

    PTAStaticMonthlyTargets[Type] = "PTA",

    TREATAS(ym, PTAStaticMonthlyTargets[YearMonth])

)

If your target table has multiple rows per month for PTA and should add up, change MAX to SUM.

6) Build the mixed chart

Use Line and clustered column chart.

• X-axis: Calendar[YearMonth]
  
  

• Column values (bars): [Actual Releases]
  
  

• Line values: [Monthly Target (PTA)]
  
  

• Filters: Year = 2026 (or a slicer on Calendar[Year])
  
  

7) Validate (don’t skip)

Create a simple table visual:

• Calendar[YearMonth]
  
  

• [Actual Releases]
  
  

• [Monthly Target (PTA)]
  
  

Check:

• Target shows the right number per month
  
  

• Actual counts match expected releases
  
  

8) Common mistakes that will break it

• Using Month names on axis without a sort column.
  
  

• Target month stored as text that doesn’t match Calendar formatting.
  
  

• Multiple PTA target rows per month but using MAX (or one row but using SUM).
  
  

• Counting rows when you actually need DISTINCTCOUNT.
  
  

Quick sanity question you should answer yourself

In SIOP, is “Customer Release Date” per Issue key unique, or do you have multiple rows per issue?
If multiple rows, you almost certainly want DISTINCTCOUNT(Issue key) for “how many releases”.

If you paste 3–4 sample rows of your Month column (exact format), I’ll give you the exact MonthStart conversion DAX/Power Query expression so you don’t fight parsing bugs.

PARAM Shavak Access

Google Form for requesting Access-> Click HERE

https://docs.google.com/spreadsheets/d/1cTVTGm8vYLb8whfkPrMR__QMBryXVBDFcXSgx4412Jg/edit?usp=sharing