Getting Value Sum from previous week

[Vicentbi]

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.

[Baskar]

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.

[Vicentbi]

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?

[Baskar]

Can u please share some sample data . I will help u 

[Vicentbi]

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

[Baskar]

Do u have Continuos date in your Table ?

[Vicentbi]

I believe so, the Date table is made by Power bi with calendar funcation.

[Baskar · Accepted 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.

[Baskar]

ALLExcpet also work .

Try this Replace Date in Allexcpet with Dimension name.

I hope this will also works for u 🙂

[Vicentbi]

Thanks Bro,

I think it just worked

I was missing 

var end_Date = MIN('Calendar'[7Day Group])   -1 <--

Thanks a lot.

[Baskar]

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))

[Vicentbi]

Since you are here

Can I ask another question?

is it possible for me to move John to the first position instead of Dimitris?

[Baskar]

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 .

[Vicentbi]

Got it thanks.

I believe either way I will need to put some sort of index on to of my Name Column. Thanks.

[Baskar]

Welcome my friend

[Vicentbi]

Welp looks like that did not work.

Please use this link.

https://drive.google.com/open?id=0BybUfI2IKhw7M0NWMlp0bG5VSHc