cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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
Super User

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.