YTD bar chart issue

[erihsehc]

hi there,

I would like to create a YTD table as below, it requires to select all months. how to get this table created by just selecting a single YTD month (for the below table, I would like to select only July)? thanks

data model

Best regards,

ER

[v-huizhn-msft]

Hi @erihsehc,

Please create a new table only including month column, and don't relate it to other tables. We name the new table as Month and add the month[month] as slicer. Then create a measure using the formula below.

New-YTD=CALCULATE([YTD_measure],Filter(Fact_table,Fact_table[month]<=SELECTEDVALUE(Month[month])))

Finally, use the [New-YTD] as value level and check if it works fine. If this still doesn't resolve your issue, please share your dummy sample table for further analysis. There is a similar thread for reference. 

Best Regards,
Angelia

[erihsehc]

hi @v-huizhn-msft, I could not get the result from this method. I have attached the data model in the top post, would you take a look? thanks

[v-huizhn-msft]

Hi @erihsehc,

Create another Calendar table, do not relate it to other tables. Create slicer using the year, month columns in new Calendar table, create a measure to get YTD value using the formula.

sales_test1 =
CALCULATE (
    Sheet1[sales],
    FILTER (
        Sheet1,
        Sheet1[year ] = SELECTEDVALUE ( New_Calendar[Year] )
            && Sheet1[data]
                <= CALCULATE ( MAX ( New_Calendar[Date] ), ALLSELECTED ( New_Calendar ) )
    )
)

You will get expected result as follows.

select 5 month, it shows from 1 to 5 month valueselect 7 month, it shows from 1 to 7 month value

Please download the attachment to get more information.

Best Regards,
Angelia

[erihsehc]

hi @v-huizhn-msft, I would like to keep the month and year slicer connected with original calendar instead of new calendar (without linking). since the original calendar connected with other visual for YTD calculation. I would like to have the YTD month slicer to get the YTD result for other visual, otherwise, I need 2 set of slicers to do the trick.  thanks for your help

Best regards,

ER

[v-huizhn-msft · Accepted solution]

Hi @erihsehc,

If you keep the month and year slicer connected with original calendar instead of new calendar (without linking), it's impossible to get the result. Because, when you select the July in slicer, all the table has been filtered only rows in July, how you can get rows in other months? Besides, you can use new calendar table to connect with other visual for YTD calculation.

Best Regards,
Angelia

[erihsehc]

thanks for the idea