Create a new table from existing table with calculated measures

[Anonymous]

Hi Community,

I need help with creating another table from the existing table with measures calculated with few conditions.

I have a dataset with 10 Years for 2 companies and calculated measure (Value_cal)

Attached the sample data

https://drive.google.com/open?id=1Ie4DUNFy5dZb5H8xIoxJI0AcLiaH9rRa 

Now I want to create another table from an existing table  with below filters

Company =" Company1" and month, year of the data set=2019- Jan and summarize calculated measure with respect to each product

Expected table

Company	Product	Date	Value_cal
Company1	Product1	2019-01-01	4
Company2	Product2	2019-01-01	4
Company3	Product3	2019-01-01

Any help would be  great full

Thanks

[Mariusz]

Hi @Anonymous 

Please see the attached Table Expression.

YourNewTable = 
ADDCOLUMNS(
    GROUPBY(
        FILTER(
            ADDCOLUMNS(
                YourTable,
                "YearMonth", 
                    VAR d = YourTable[Date]
                    RETURN YEAR( d ) * 100 + MONTH( d )
            ),
            YourTable[Company] = "Company1"
            && [YearMonth] = 201901
        ),
        YourTable[Company],
        YourTable[Product],
        [YearMonth]
    ),
    "Value_cal", CALCULATE( SUM( YourTable[Value_cal] ) ) -- or replace Calculate with your Measure 
)   

Regards,
Mariusz

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

[Anonymous]

@Mariusz 

Thanks for the reply,

In my original dataset,

To calculate the final Value, I have Fixed the values with respect to year and each category and also used all except functions.

As per the above solution, it was giving wrong values 

Any solution would be grateful

Thanks

Yamuna