Get Sales for previous period for a specific Segment.

[CorneldaFaber]

Hi,

I have a table with sales from a specific segment from a specifc date. I am trying to compute the sales from that specific segment for the previous period (the periods are random months). I have tried indexing, ranking and a bunch of other stuff but haven't gotten a solution yet. PLEASE HELP!?

It should look like this...

DateNew	Core Sort Order	TotalAdvances_Current	TotalAdvances_Previous
2021/01/01 00:00	a. Core and Thin	R1 458 458 748 780
2021/01/01 00:00	b. Care	R587 048 787 402
2021/01/01 00:00	c. Non-Core	R503 110 269 031
2021/04/01 00:00	a. Core and Thin	R1 476 293 061 269	R1 458 458 748 780
2021/04/01 00:00	b. Care	R594 140 554 312	R587 048 787 402
2021/04/01 00:00	c. Non-Core	R499 131 753 788	R503 110 269 031
2021/06/01 00:00	a. Core and Thin	R1 492 034 668 624	R1 476 293 061 269
2021/06/01 00:00	b. Care	R599 276 245 947	R594 140 554 312
2021/06/01 00:00	c. Non-Core	R470 619 814 057	R499 131 753 788
2021/10/01 00:00	a. Core and Thin	R1 506 174 347 602	R1 492 034 668 624
2021/10/01 00:00	b. Care	R614 210 342 518	R599 276 245 947
2021/10/01 00:00	c. Non-Core	R491 450 218 097	R470 619 814 057
2021/12/01 00:00	a. Core and Thin	R1 550 784 408 578	R1 506 174 347 602
2021/12/01 00:00	b. Care	R609 304 560 888	R614 210 342 518
2021/12/01 00:00	c. Non-Core	R486 522 888 568	R491 450 218 097

[Ashish_Mathur]

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[TotalAdvances_Current],Data[DateNew],CALCULATE(MAX(Data[DateNew]),FILTER(Data,Data[Core Sort Order]=EARLIER(Data[Core Sort Order])&&Data[DateNew]<EARLIER(Data[DateNew]))),Data[Core Sort Order],Data[Core Sort Order])

Hope this helps.

[CorneldaFaber]

Hi @Ashish_Mathur 

Thanks for your post. Like I said above I figured out a way which is also working fine and that is very similar to your response. 

Thanks again. 

[v-eqin-msft · Accepted solution]

Hi @CorneldaFaber ,

According to my understanding, you want to get a value of the latest date before current date for each type.

Please try:

TotalAdvances_Previous =
VAR _lastDate =
    MAXX (
        FILTER (
            'Table',
            [Core Sort Order] = EARLIER ( 'Table'[Core Sort Order] )
                && [DateNew] < EARLIER ( 'Table'[DateNew] )
        ),
        [DateNew]
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[TotalAdvances_Current] ),
        FILTER (
            'Table',
            [Core Sort Order] = EARLIER ( 'Table'[Core Sort Order] )
                && [DateNew] = _lastDate
        )
    )

Output:

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

[CorneldaFaber]

Hi @v-eqin-msft 

Thanks so much. This work. Before you posted I figured out the problem and did something similar. Thanks so much. 🙂