Forum Discussion

iozkan's avatar
iozkan
Regular Visitor
8 years ago
Solved

Defining basis month or year and comparing

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 y...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    iozkan

     

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

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
iozkan
Regular Visitor
8 years ago

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's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi iozkan

     

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

     

    I will try to fix it

  • iozkan's avatar
    iozkan
    Regular Visitor
    8 years ago

    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.