cancel
Showing results for
Did you mean:
Post Prodigy

## calculate measure sum dates based on a measure

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?

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Post Prodigy

Thanks, this works for me!

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Super User III

Tablix?  Is this for Paginated reports?

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors