calculate measure sum dates based on a measure

[dobregon]

Hi guys,

I have a little problem with a calculate query. I dont know if it is possible but maybe someone can help me on this

I have a calendar table with all de dates of this year and i have a week selector, but my client wants to see a week table and a YTD table that shows fromt he 1st of the year to the last day of the week selected. To do that i have created a measure that if the date is >=1st Jan and <= lastday of the week, = 1 aand if not = 0. After that i filter my tablix with that measure = 1 (the tablix is by month and date)

But now... i need to have a card with the total of the Sales YTD (but aplyiend that filter). The problem is that i can't put the dates in the card so, the filter is not running and it gives to me the yTD value for all the year, not depending of the Week selected.

I'm looking for if exist the possibility to create a calculate like--

CALCULATE(SUM(SALES), FILTER(TABLE, TABLE[DATE] = TABLE[MEASUREYTD] = 1 ) something that the system analyze every day individuale if the measureytd=1 for that date and then sum the value.

can someone helpme on this?

[v-easonf-msft · Accepted solution]

Hi,, @dobregon 

You  may need to create a summarize table and than calculate the measure in this summarize table , then apply this new measure to card.

The formula is similar to the following：

M_TYTD =
VAR tab1 =
    SUMMARIZE (
        TABLE,
        TABLE[Date],
        TABLE[sales],
        TABLE[column1],
        TABLE[columnX],
        "M_YTD", [MEASUREYTD]
    )
RETURN
    SUMX ( FILTER ( tab1, [M_YTD] = 1 ), TABLE[sales] )

If it doesn't meet your requirement, please share a sample file  for further research. 

Best Regards,
Community Support Team _ Eason

[dobregon]

Thanks, this works for me!

[lbendlin]

Tablix?  Is this for Paginated reports?

Please provide sample data in usable format (not as a picture) and show the expected outcome.