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.
Gross Sales Value PY:= var currentbytiweek = SELECTEDVALUE('Dim Date Weekly'[WEEK_NUM]) var currentbeytiyear = SELECTEDVALUE('Dim Date Weekly'[YEAR]) return calculate ([Gross Sales Value], filter (all('Dim Date Weekly'),'Dim Date Weekly'[WEEK_NUM] =currentbytiweek && 'Dim Date Weekly'[YEAR] = currentbeytiyear -1 )). This code will return the lookup value for last year "only one week" based on selected value condition above. my question :: i want to return multiple values for also perivous year for ex : week1+week2+week3 any help please.
talking about total sum if i select multiple weeks
Solved! Go to Solution.
@v-xicai @amitchandak i solved it yesterday , it is all about replace"Values" with "selectedvalue" to return mutiple values , and replace = with "in" .
@AhmedNagy , if you have date use year behind measure
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Year))
Or
Sem week day Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-364,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
@amitchandak i'm using custom calendre , which means depending on "dateadd" function will not return a good solution , and i depend only on weeks,
I want to return the sum of weeks from privous year "if i select multiple weeks"
@AhmedNagy , Typically use week rank in case week. And Week year is 52 weeks behind
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Same Week last year= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-52))
But on multi-select this will work for view by week
Is same weekday -364 day is fine or there is some other logic
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),Date[Date] -364)
@amitchandak i'm very thanks for your respoonse , let me tell you that measure is running successfully in single selection and return accurate data ,
my issue is about multi selection : in this case the result does not show "which suppose to be accumalated in weeks pervious year"
Hi @AhmedNagy ,
You may create measure like DAX below.
Gross Sales Value PY= CALCULATE([Gross Sales Value], FILTER(ALLSELECTED('Dim Date Weekly'),'Dim Date Weekly'[WEEK_NUM] <=MAX('Dim Date Weekly'[WEEK_NUM]) && 'Dim Date Weekly'[YEAR] = MAX('Dim Date Weekly'[YEAR]) -1 ))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai thanks fro your response , but it gives wrong answer because it always return <= the selected week num
@v-xicai @amitchandak i solved it yesterday , it is all about replace"Values" with "selectedvalue" to return mutiple values , and replace = with "in" .
@AhmedNagy , Thanks for Sharing. Kudos !!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |