cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon
Post Prodigy
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
v-easonf-msft
Community Support
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

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
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

 

View solution in original post

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)
lbendlin
Super User III
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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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