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
I have been trying to get values for previous week, unfuntuanlly it was not as easy as getting from previous month, with previousmonth function.
I alreday used a grouped date on the left side as currently date/week.
I have tried use datesbetween and use dateadd filters but they seems unable to collect values outside the current date.
CALCULATE(sum(table[Quantity]), DATESBETWEEN(table[7Days], DATEADD(FIRSTDATE(table[CurrentDate]),-7,DAY),DATEADD(LASTDATE(table[CurrentDate]),-7,DAY))
But if I use static value date(Year,Month,Day) for the startdate and enddate in datesbetween, I was able to get value outside the currently date.
Note: 7days is grouped CurrentDate
Any suggestion is appreciated.
Solved! Go to Solution.
Sorry Mistake is my side.
Try this
Pre Sales =
var End_Date = MIN('Week Sales'[StartOfWeek]) -1
var Start_Date = End_Date - 6
return
CALCULATE(SUM('Week Sales'[Sales]),DATESBETWEEN('Week Sales'[Date],Start_Date,End_Date),ALLSELECTED('Week Sales'[StartOfWeek]))
Small Change in your already existing formula .
Change ALLExcpet into ALLSelected, It should work
If not let me know dude.
Cool, Try this one .
Days Count =
var end_Date = MIN('Date Master'[Date]) -- To get End of previous week
var start_Date = end_Date -6 -- To get Start of Previous week in 7 Days Difference.
return CALCULATE(COUNT('Date Master'[Date]),ALLEXCEPT('Date Master','Date Master'[Date]), DATESBETWEEN('Date Master'[Date],start_Date,end_Date))
Note :
Your calculation also correct have to do one correction.
Add ALL(Table Name) in your calculation, or AllExcept (Perticular Date Column)
Try this too.
Thanks a lot Baskar,
I jusat tried your method but it return the same result as the current week.
I was able to get the different with the following
Differ = VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1,BLANK(),
(SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALLSELECTED('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek)))))
Found it in https://community.powerbi.com/t5/Desktop/Week-on-Week-Month-on-Month-Analysis/td-p/79063
But now I have another issue.
As you can see I have mutiple people in the chart and the above method will give provide me all the sales of all no that single person's sales.
I know I can add
WEEKNUM( 'Fact'[Date] ) = PreWeek) && 'Fact'[Name]="Name here"
to correct the filter.
But I was unable to find a way to get the current column name to var currentname
Is there a way to do it?
Can u please share some sample data . I will help u
Strange I do not see where I can attach a file.
I'm attaching it as a photo for now.
Do not know if it works.
This is not the data I was using orgnially but structure should be the same
Do u have Continuos date in your Table ?
I believe so, the Date table is made by Power bi with calendar funcation.
Sorry Mistake is my side.
Try this
Pre Sales =
var End_Date = MIN('Week Sales'[StartOfWeek]) -1
var Start_Date = End_Date - 6
return
CALCULATE(SUM('Week Sales'[Sales]),DATESBETWEEN('Week Sales'[Date],Start_Date,End_Date),ALLSELECTED('Week Sales'[StartOfWeek]))
Small Change in your already existing formula .
Change ALLExcpet into ALLSelected, It should work
If not let me know dude.
ALLExcpet also work .
Try this Replace Date in Allexcpet with Dimension name.
I hope this will also works for u 🙂
Thanks Bro,
I think it just worked
I was missing
var end_Date = MIN('Calendar'[7Day Group]) -1 <--
Thanks a lot.
Welcome bro.
This is very simple try this one.
Name Test =
var End_Date = MIN('Calendar'[7Day Group]) -7
return
CALCULATE(SUM('Fact'[Sales]),FILTER(ALL('Calendar'[7Day Group]),'Calendar'[7Day Group] =End_Date))
Since you are here
Can I ask another question?
is it possible for me to move John to the first position instead of Dimitris?
Yes we can , But for that have create one calculated column like
if ( Name="A",1,2) like until how many names u have . then u have short the name with this value asc or desc.
It will only work if u have constant name , name is changing dynamically it will not helping u .
Got it thanks.
I believe either way I will need to put some sort of index on to of my Name Column. Thanks.
Welcome my friend
Welp looks like that did not work.
Please use this link.
https://drive.google.com/open?id=0BybUfI2IKhw7M0NWMlp0bG5VSHc
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |