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 dat...
Need Help
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 ,

 

Thanks for the mail , Can you please share me the pbix file ?

 

Regards

SHIVA

  • 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