Show data until selected month

[ncbshiva]

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 

[v-yulgu-msft]

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]

Hi @v-yulgu-msft ,

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

Regards

SHIVA

[ncbshiva]

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

[v-yulgu-msft · Accepted solution]

Hi @ncbshiva ,

Have uploaded the sample .pbix file for your reference.

Regards,

Yuliana Gu