Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Thanks, this works for me!
Tablix? Is this for Paginated reports?
Please provide sample data in usable format (not as a picture) and show the expected outcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |