Creating a virtual table with deal ids for further calculations

[Elisa_H4]

Hi everyone. I have an issue with my DAX measure that is only reverting blanks.

My aim is to create a measure that shows how many deals that where open a year ago, have been won since then. So I somehow need to create a virtual table possibly with calculatetable or summarize that would contain distinct Deal IDs of all open deals from a year ago. Then I want to see how many of those deals are currently Won, and calculate their ARR. 

Im working with two tables. Sales_Deals and Date_Table_Snapshot_Date. They have a one-to-many relationship. The Sales_Deals table includes monthly snapshots of all deals, while the date table has date values only. There can also be duplicate rows of Deal IDs per snapshot, in case one deal includes several products.

Here is my current attempt which is not working: 

WonFromOpenFunnelPreviousYear =

VAR CurrentSnapshot = MAX(Date_Table_Snapshot_Date[Short_Date])

VAR IDsLastYear=

    CALCULATETABLE(

        VALUES(Sales_Opportunities[Deal_ID]),

        Sales_Opportunities[StateCode] = "Open",

        FILTER(

            ALL(Date_Table_Snapshot_Date),

            SAMEPERIODLASTYEAR(Date_Table_Snapshot_Date[Short_Date]))

    )

VAR Current_Won =

    CALCULATE(

        SUMX(

            IDsLastYear,

            CALCULATE(

                [ARRTotal],
                Sales_Opportunities[statecode] = “Won”,

                FILTER(

            ALL(Date_Table_Snapshot_Date),

                Date_Table_Snapshot_Date[Short_Date] = CurrentSnapshot

            )

        )

    )

    )

RETURN

        Current_Won
Please see some sample data here https://drive.google.com/file/d/1RtgNnigPesIQNsLvUGY5p8koPJLL83Fo/view?usp=sharing 
If you have any clues on how to get this to work, I would appreciate it so much! Thank you.

[lbendlin]

Your approach sounds reasonable. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Elisa_H4]

Thank you for your reply! I have uploaded here a file with only relevant columns, and for instance the ARR measure is simplified (not taking into account different currencies). I think it should work with this too.

I have now also been trying with 2 calculatetables and intersect in order to get the desired outcome. I have managed to understand that atleast the sameperiodlastyear/dateadd is not working properly. If I split the DAX into 2 parts for troubleshooting, I do get values for ARR, however for the current time period, not for last year. So atleast that is not working within the calculatetable it seems, might be something else as well.

Please see data sample link !

Looking forward to your reply !

https://drive.google.com/file/d/1RtgNnigPesIQNsLvUGY5p8koPJLL83Fo/view?usp=sharing 

[lbendlin]

You have too much data

Instead of doing all these snapshots you should only record the changes, either in status or in value.  Then you can disconnect your calendar table and create your own snapshots for how your funnel looked like a year ago etc.

SAMEPERIODLASTYEAR is used for cumulative values like revenue,  not for fast changing values like opportunity value.

I simplified some of your calculated tables.

[Elisa_H4]

Ok thank you for taking a look! Unfortunately we have only available history data in such snapshots, and they are pretty big so... The Calendar table also connects to other tables, which is why I prefer to use those in case I need to bring in other data at some point... though for this dashboard that Im building it would probably not be necessary to use the calendar table to join with other data. Anyway, I will continue working on the DAX.