Not able get last year complete number for current financial year

[Madhu155154]

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

    )

[Rakesh1705]

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 

[Madhu155154 · Accepted solution]

Thank you @Rakesh1705 ,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] )
    )
)

[Anonymous]

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.

[Madhu155154]

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] )
    )
)

[Rakesh1705]

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.

[Madhu155154]

@Rakesh1705 , 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

[Greg_Deckler]

@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-TIT...