topic Not able get last year complete number for current financial year in DAX Commands and Tips

Not able get last year complete number for current financial year

Madhu155154 — Mon, 01 Jul 2024 15:01:54 GMT

Hi Every I am trying to get the previous Year running total for the next fiscal year.

However I am not able to get it for the current fiscal year.

The formula I used:

=VAR _date=

MAXX(

SAMEPERIODLASTYEAR(DATESYTD(

'Calendar'[Date]

)),

'Calendar'[Date]

)

RETURN

CALCULATE (

[Total_Employee Count ],

FILTER (

ALL('Calendar'),

'Calendar'[Date] <= _date

)

Re: Not able get last year complete number for current financial year

Greg_Deckler — Mon, 01 Jul 2024 16:44:34 GMT

Madhu155154 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Not able get last year complete number for current financial year

Anonymous — Mon, 01 Jul 2024 16:45:05 GMT

Source Data

Calculating Prev FY column

Calculating Prev FY Value

Calculating Current FY running total

Calculating Prev FY running total

Creating FY Table

Creating relationship between FY[FY] and table[FY]

Required table is ready.

Re: Not able get last year complete number for current financial year

Madhu155154 — Tue, 02 Jul 2024 01:28:29 GMT

Anonymous , Thank you for replying, the issue is, i have further granularity of months.

if you see below pic, for cureent year, i have months till october,

Because of that, it is taking previous year data till october, showing it for current year.

But what i want is, to ignore the month's, show me entire value of last year to current year

i want 186 instead of 145

Re: Not able get last year complete number for current financial year

Anonymous — Tue, 02 Jul 2024 08:38:55 GMT

Hi all,Thank you for your quick reply, I will add more.

Hi Madhu155154 ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a date table.

Calendar = VAR _table = CALENDAR(DATE(2022,1,1),DATE(2024,12,31)) RETURN ADDCOLUMNS(_table, "Column", SWITCH(TRUE(), YEAR([Date]) = 2022,"2022-2023", YEAR([Date]) = 2023,"2023-2024", YEAR([Date]) = 2024,"2024-2025" ), "Month", SWITCH(TRUE(), MONTH([Date]) = 1 ,"Jan", MONTH([Date]) = 2 ,"Feb", MONTH([Date]) = 3 ,"Mar", MONTH([Date]) = 4 ,"Apr", MONTH([Date]) = 5 ,"May", MONTH([Date]) = 6 ,"Jun", MONTH([Date]) = 7 ,"Jul", MONTH([Date]) = 8 ,"Aug", MONTH([Date]) = 9 ,"Sep", MONTH([Date]) = 10 ,"Oct", MONTH([Date]) = 11 ,"Nov", MONTH([Date]) = 12 ,"Dec" ) )

2.Use the following DAX expression to create measures.

_Value = SUM('Table'[Value])

Prev_Year_Value = CALCULATE([_Value],SAMEPERIODLASTYEAR('Calendar'[Date]))

3.Final output

If I understand wrongly, please share simple data.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Not able get last year complete number for current financial year

Anonymous — Tue, 02 Jul 2024 15:54:11 GMT

Hi I guess I have solved as per your requirement. Pls check the below snap.

For detail pls check the full video from the link below
Previous Year Running Total

Re: Not able get last year complete number for current financial year

Madhu155154 — Wed, 03 Jul 2024 11:59:30 GMT

Thank you Anonymous ,i was your video it was good but my requirement was different, from the below, code, i was able to achieve the result

CALCULATE (
[Running_Total_Employees],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal Year] <MAX ( 'Calendar'[Fiscal Year] )
)
)

Re: Not able get last year complete number for current financial year

Madhu155154 — Wed, 03 Jul 2024 12:01:06 GMT

Thank you Anonymous , i was able achieve my result by below code.

CALCULATE (
[Running_Total_Employees],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal Year] <MAX ( 'Calendar'[Fiscal Year] )
)
)