Microsoft Power BI - DAX - develop a dataset using variables - NATURALJOIN, GENERATE, GENERATEALL

Hello Ben,

I referred to your solution, and zeroed-in on the logic.

I assume that the 2 variables (VAR_SourceData, VAR_ReferenceYearLookup) are tables, and have this code below for a Calculated Table:

DesiredOutput_CT = 

VAR src=DISTINCT(

                    SELECTCOLUMNS(

                                   VAR_SourceData,

                                   "City", [City],
                                   "Product", [Product],
                                   "Quantity", [Quantity]

                                  )

                   )

VAR cj = CROSSJOIN(src, VAR_ReferenceYearLookup)

VAR t1 = SELECTCOLUMNS(

                        cj,

                        "Concat", [City] & "-" & [Product] & "-" & [Year_LKP],
                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Year", [Year_LKP]

                      )

VAR t2 = SELECTCOLUMNS(

                        VAR_SourceData,

                        "Concat", [City] & "-" & [Product] & "-" & [Year],
                        "Price", [Price]

                      )

VAR t3 = SELECTCOLUMNS(

                        t1,

                        "Concat", [Concat] & "Z",
                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Year", [Year]

                      )

VAR t4 = SELECTCOLUMNS(

                        t2,

                        "Concat", [Concat] & "Z",
                        "Price", [Price]

                      )

VAR t5 = NATURALLEFTOUTERJOIN(t3,t4)

VAR t6 = SELECTCOLUMNS(

                        t5,

                        "City", [City],
                        "Product", [Product],
                        "Quantity", [Quantity],
                        "Price", [Price],
                        "Year", [Year]

                      )

RETURN t6

This gives me the correct output. But thanks very much for your codes. Really appreciate it.