Dax Filter - Comparing sum on specific date against month start

[awolf88]

Dear team,

need som help from you geniusses agian as I can't seem to figure this out for the life of me: 

I have a simple table as shown below (except yellow - it is what im trying to achieve):

The table displays several date entries and captures the remainder of "Open Orders" per that date alongside the "sales" from that very same date. Both added together result in what I project to be the outcome by the month's end per that according date. 

Now what I'm looking for is the change in % vs. the starting value of the month. So Projected value from 02.11.2022 vs. 01.11.2022, or projected 04.11.2022 vs. 01.11.2022, etc. etc.

What I've tried was coding it in Dax as such:

Delta month start = 
sum(Projected) / 
calculate( sum (Projected), FIRSTDATE(Date_Archived))  ) - 1

It doesnt seem to be running my way tho. 

Could anyone kindly point me in the right direction here, please?

Thanks so much in advance!

Best,

Alex

[awolf88]

Thank you so much for your help, Jianbo! Much appreciated!

[v-jianboli-msft · Accepted solution]

Hi @awolf88 ,

Please try:

Measure =
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Projected end of month] ),
        FILTER (
            ALL ( 'Table' ),
            [Date_Achieved] = MINX ( ALL ( 'Table'[Date_Achieved] ), [Date_Achieved] )
        )
    )
VAR _b =
    SUM ( 'Table'[Projected end of month] )
RETURN
    DIVIDE ( _b - _a, _a )

Final output:

Best Regards,

Jianbo Li

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