topic Show values until recent month only in a matrix. in DAX Commands and Tips

Show values until recent month only in a matrix.

kristel_tulio — Wed, 08 Feb 2023 06:47:41 GMT

Hi I would like to ask for your help. I have this covenant financial report and I need to show the values only until the recent month of the current year. In my visuals I should only have values until December 2022, but it fills the whole year with the same value as the current month and the total was messed up.

I have 4 tables BS Template -Accounts - Journals - Dates

Data Model

This is my measure:

Covenant M =
var _dummyaccount = SELECTEDVALUE('BS Template'[Items Normalize])
return
SWITCH(
TRUE() ,
_dummyaccount = "Total Assets", [Total Assets $$],
_dummyaccount = "Total Liabilities", [Total Liabilities $$],
_dummyaccount = "Total Equity",[Total Equity $$],
_dummyaccount = "Total Tangible Assets", [Total Tangible Assets],
_dummyaccount = "Net Tangible", [Net Tangible],
_dummyaccount = "In $ terms to find", [In $ terms to find],
_dummyaccount = "Tangible Asset %", FORMAT([Tangible Asset %], "0.00%"),
_dummyaccount = "Shortfall", FORMAT([Shortfall], "0.00%"),

CALCULATE([Totals])
)

Re: Show values until recent month only in a matrix.

jaweher899 — Wed, 08 Feb 2023 10:38:25 GMT

please try

Covenant M = var _dummyaccount = SELECTEDVALUE('BS Template'[Items Normalize]) var _recentmonth = MAX('Dates'[Month]) return SWITCH( TRUE() , _dummyaccount = "Total Assets", [Total Assets $$], _dummyaccount = "Total Liabilities", [Total Liabilities $$], _dummyaccount = "Total Equity",[Total Equity $$], _dummyaccount = "Total Tangible Assets", [Total Tangible Assets], _dummyaccount = "Net Tangible", [Net Tangible], _dummyaccount = "In $ terms to find", [In $ terms to find], _dummyaccount = "Tangible Asset %", FORMAT([Tangible Asset %], "0.00%"), _dummyaccount = "Shortfall", FORMAT([Shortfall], "0.00%"),

CALCULATE([Totals], FILTER('Dates', 'Dates'[Month] <= _recentmonth)) )

Re: Show values until recent month only in a matrix.

kristel_tulio — Wed, 08 Feb 2023 23:29:01 GMT

Hi jaweher899

Thank you for helping! but it seems the measure is not working still.