Forum Discussion

Vicentbi's avatar
Vicentbi
Frequent Visitor
9 years ago
Solved

Getting Value Sum from previous week

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's avatar
    Baskar
    9 years ago

    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.

     

     

     

     

15 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    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's avatar
      Vicentbi
      Frequent Visitor
      9 years ago

      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's avatar
        Baskar
        Resident Rockstar
        9 years ago

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