How to get Balance sheet comparative number at year end instead of actual month end

HKBros — Tue, 03 Oct 2023 04:54:39 GMT

By adding "-1" in below DAX formula, I get Last year comparative number for PL and BS. For PL, that is correct but for balance sheet I want year end number.

For example,

In slicers I select "start month 1 end month 4 start year 2023 end year 2023",

the results I get in matrix for comparative numbers PL YTD April 2022, Balance sheet 30th April 2022

Require to update DAX formula to get balance sheet 31st Dec 2022 instead of 30th april 2022.

Below is DAX formula used:

LY Amount in AED = var figure = [AEDin] var startYR=[Start Year]-1 var endYr=[End Year]-1 var startMT=[Start Month] var endMt=[End Month] var PLBS= [BS/PL] return if(PLBS="PL", CALCULATE(SUM('Combined Cubes'[Value]),'Date Master'[Date]>=date(startYR,startMT,1),'Date Master'[Date]<=EOMONTH(date(endYr,endMt,1),0)), CALCULATE(SUM('Combined Cubes'[Value]),'Date Master'[Date]>=date(2022,1,1),'Date Master'[Date]<=EOMONTH(date(endYr,endMt,1),0)))/figure

Re: How to get Balance sheet comparative number at year end instead of actual month end

some_bih — Tue, 03 Oct 2023 11:13:18 GMT

Hi HKBros as you want to "deal" with dates, one of possible solution is to:

- create Calendar / Date ta ble, check link below.

- after that create measure for year end 2022

Measure test for Balance sheet 2022=
CALCULATE(
SUM('Combined Cubes'[Value]),
PLBS="PL", --change to your table / column accordingly
CALCULATETABLE (
ENDOFYEAR ( 'Date'[Date] ),--Date should be your Calendar / Date table with [Date] column, otherwise change accordingly
'Date'[Date] = DATE ( 2022, 3, 10 ) -- change year
)
)

Create Calendar Table Microsoft Learn

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

topic Re: How to get Balance sheet comparative number at year end instead of actual month end in DAX Commands and Tips

How to get Balance sheet comparative number at year end instead of actual month end

Re: How to get Balance sheet comparative number at year end instead of actual month end