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

Hi iozkan

 

Where did the error show up?

 

In the NEW TABLE?

iozkan's avatar
iozkan
Regular Visitor
8 years ago

Yes, in the new table. 

 

 

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

    Hi iozkan

     

    I believe you are doing it in Excel?

    How are you creating the NEW 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