Forum Discussion

ncbshiva's avatar
ncbshiva
Advocate V
7 years ago
Solved

Show data until selected month

Hi Team,

 

I have two measures "Compare From" and "Compare To" in my Fact Table. I need to show the data in the "Compare From" until my selected month. And my Compare To measure should show the data for all the months for the selected Year and it should ignore the month filter.

 

I have a month and year filter in my report. The relationship with my Date dimension is 1 to Many to my Fact Table.

 

For example : If my month filter is "May" and Year filter is "2019" then my "Compare From" measure should show the data from January 2019 till May 2019 selected month and year. Here my "Compare To" measure should show the data from Jan 2019 till Dec 2019.

 

Please help me with the DAX formula.

 

Regards

SHIVA 

 

 

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi ncbshiva ,

     

    Add a calendar table which is not linked to fact table. Add [YearNo] and [MonthName] from 'Date dimension' into slicers.

    Date dimension =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "YearNo", YEAR ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMM" ),
    "MonthNo", MONTH ( [Date] )
)

    Then, create measures as below.

    Compare from =
IF (
    SELECTEDVALUE ( 'Fact'[Date].[Year] )
        = SELECTEDVALUE ( 'Date dimension'[YearNo] )
        && SELECTEDVALUE ( 'Fact'[Date].[MonthNo] )
            <= SELECTEDVALUE ( 'Date dimension'[MonthNo] ),
    SUM ( 'Fact'[Amount] ),
    BLANK ()
)

Compare to =
IF (
    SELECTEDVALUE ( 'Fact'[Date].[Year] )
        = SELECTEDVALUE ( 'Date dimension'[YearNo] ),
    SUM ( 'Fact'[Amount] ),
    BLANK ()
)

    Best regards,

    Yuliana Gu

      • ncbshiva's avatar
        ncbshiva
        Advocate V
        7 years ago

        Hi v-yulgu-msft ,

         

        I tried in my pbix file it is not working , so i am asking can you please share me the pbix file which you have created these Compare To and Compare From measures.

         

        Regards

        SHIVA