DAX - FY Year LYTD population

[venug]

Hi everyone,

i am stuck with one scenario, i have sample data.

i am unble to get lytd population like below table. i have only fiscal year, there is no date field.

Fiscal Year	Population	Population LYTD
FY14/15	19518
FY15/16	19956	19518
FY16/17	20374	19956
FY17/18	20800	20374
FY18/19	21224	20800
FY19/20	21650	21224
FY20/21	21650	21650
FY21/22	21650	21650
FY22/23	0	21650
FY23/24	0	0
FY24/25	0	0
Grand Total	166822	166822

Please help on this issue, thnaks in advance.

Thanks

Venu

[V-pazhen-msft]

@venug 

I would need a use rankx create an index. The following are 2 calculate columns, give it a try.

rankFY = RANKX('Raw-Data',[Time Name],,ASC,Dense)

LastFY population = CALCULATE(SUM('Raw-Data'[Population]),FILTER('Raw-Data','Raw-Data'[Minor Country] = "AFGHANISTAN"),FILTER('Raw-Data','Raw-Data'[rankFY]=EARLIER('Raw-Data'[rankFY])-1))

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[venug]

@V-pazhen-msft  thanks for respond.

i am getting LYTD values very huge. please correct my pbix file.

Thanks in advance.

Regards

Venu

[V-pazhen-msft · Accepted solution]

@venug 

You need to add a filter to point the minor country. Then select "don't summarize" for the column in the values field.

LastFY population = CALCULATE(SUM('Raw-Data'[Population]), FILTER('Raw-Data','Raw-Data'[rankFY]=EARLIER('Raw-Data'[rankFY])-1),'Raw-Data'[Minor Country]=EARLIER('Raw-Data'[Minor Country]))

Best regards
Paul Zheng

[Greg_Deckler]

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

[venug]

@Greg_Deckler  Thanks for respond.

your stuff is awesome.