Defining basis month or year and comparing

[iozkan]

Hi guys,

I have a table of sales figures with dates (monthly, quarterly, yearly) and I'd like to choose a basis month or year (which will be 100 and should change according to selected month or year but basis should be 100) then compare it as in below. Many thanks!

[Zubair_Muhammad · Accepted solution]

@iozkan

Bascially I have used a New Table of Dates to slice the original Table

A New Table (Calculated Table) was created from the Modeling Tab>>> NEW TABLE

New_Table =
ALL ( TableName[Date] )

Then following MEASURE was added to Original Table

Index =
VAR selectedmonthSales =
    CALCULATE (
        SUM ( TableName[Sales] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date] = SELECTEDVALUE ( 'New_Table'[Date] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Date] ) = SELECTEDVALUE ( 'New_Table'[Date] ),
        100,
        SELECTEDVALUE ( TableName[Sales] ) * 100
            / selectedmonthSales
    )

[Zubair_Muhammad]

Hi @iozkan

See the attached pbix file here

or

See the Excel file here

Hope it helps

[Zubair_Muhammad · Accepted solution]

@iozkan

Bascially I have used a New Table of Dates to slice the original Table

A New Table (Calculated Table) was created from the Modeling Tab>>> NEW TABLE

New_Table =
ALL ( TableName[Date] )

Then following MEASURE was added to Original Table

Index =
VAR selectedmonthSales =
    CALCULATE (
        SUM ( TableName[Sales] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date] = SELECTEDVALUE ( 'New_Table'[Date] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Date] ) = SELECTEDVALUE ( 'New_Table'[Date] ),
        100,
        SELECTEDVALUE ( TableName[Sales] ) * 100
            / selectedmonthSales
    )

[Zubair_Muhammad · Accepted solution]

@iozkan

Using Slicer from New Table, now you can get the required Index Measure in your Original Table

[iozkan]

Many thanks! Is ALL function limited with only one year or can i add more date? I've tried this in power pivot but "a table of multiple values supplied where a single value is expected" error showed up.

[Zubair_Muhammad]

Hi @iozkan

Where did the error show up?

In the NEW TABLE?

[iozkan]

Yes, in the new table. 

[Zubair_Muhammad]

Hi @iozkan

I believe you are doing it in Excel?

How are you creating the NEW TABLE?

[Zubair_Muhammad]

@iozkan

In Excel you can simply copy the Dates Column from your Original table and paste it as NEW TABLE and then Add it to the DATA MODEL.

See the Excel file I attached above

[iozkan]

Hi,

Yes I've copied the date from sales table and copied to a new sheet, added this table to the data model. When I try to add date column in pivot table below error pops up

[Zubair_Muhammad]

Hi @iozkan

Please could you upload your file to google drive or onedrive and share link here

I will try to fix it

[iozkan]

Hi,

I have two more questions sadly.

I've found the problem, i had duplicate values in sales table, some products are sold in many stores. If I filter single product and store i have the values, otherwise "duplicate"problem occurs. What should I do for comparing all portfolio with index date then, I can't remove duplicate ones. (1)

I have 3 different date tables; sales, index, calendar. Calendar date is daily, sales date is monthly and now index date is same as sales. I also want to compare sales data quarterly, and index should be 100 for quarterly. Is it possible to use a general index date table for all dates? (2) My fx table has daily rates and if I use date and fx in pivot table, fx is listed as average and I want index date to be available for all.

Many many thanks.