Not calculate MAT growth % if number of months are less then 12

[danisharoon]

I have some data on a month on month basis. I need to calculate the MAT growth for the selected Month year. Formula for which will be as follows:

MAT growth % = (MAT CY - MAT PY) / MAT PY

By CY we mean Current year, and by PY we mean Past year. For instance, if I select Jul'22, then MAT CY will compute a sum across Aug'21-Jul'22, and MAT PY will compute a sum across Aug'20-Jul'21.

MAT growth % should only be possible if values of exactly 12 monthly intervals are present in PY and CY.

Output from my DAX is as follows:

Greyed out cells are not desirable as they don't satisfy the 12 monthly interval requirement. Hence given the data, MAT growth % should be possible only for June and July'22.

DAX formulas are as follows

FACT TP = 
CALCULATE(
    SUM(fact_size_markets[Fact value]),
    DATESINPERIOD(
        dim_my[MY name],
        MAX(dim_my[MY name]),
        -1,
        YEAR
    )
)

FACT LP = 
CALCULATE(
    SUM(fact_size_markets[Fact value]),
    DATESINPERIOD(
        dim_my[MY name],
        LASTDATE(DATEADD(dim_my[MY name], -1,YEAR)),
        -1,
        YEAR
    )
)

Link to the pbix file is as follows: https://drive.google.com/file/d/1ifP1Knp_fh02i1vqbg-jeFYXqD-y2CPP/view?usp=sharing

Thanks

[amitchandak]

@danisharoon , Try like

FACT LP =
var _max1 = max(dim_my[MY name])
var _max = date(year(_max1) -1, month(_max1), Day(_max1))
return
CALCULATE(
SUM(fact_size_markets[Fact value]),
DATESINPERIOD(
dim_my[MY name],
_max,
-1,
YEAR
)
)

make sure dim_my is marked as date table and date in visual is used from dim_my

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[danisharoon]

No improvement there. Its returning the same values.