Forum Discussion

NL-Nicolas's avatar
NL-Nicolas
Helper I
4 years ago

NetSales by a date in Column

Hey all!

 

I have a calculated column here:
Last Week = CALCULATE(sum(DayfileSummary[DF_BusinessDay]), FILTER(ALL(DayfileSummary[DF_BusinessDay]),DayfileSummary[DF_BusinessDay]=max(DayfileSummary[DF_BusinessDay])-366))
and when I go to attach it to my field DayfileSummary[NetSales] it will not filter in anyway.

 

My end goal is to filter the number in each row for the column NetSales by the date in the column DayfileSummary[Last Week]

It is a Year over Year change analyisis for the same day one year ago. In this case Last week is finding their last day on the same fiscal calendar. When I try quick calculate, I cannot use 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi NL-Nicolas ,

     

    I think you can try code as below to catch the same day one year ago based on [Last Week].

    DATE(YEAR(MAX('DayfileSummary'[Last Week])-1 ),MONTH(MAX('DayfileSummary'[Last Week]) ),DAY(MAX('DayfileSummary'[Last Week])))

    Please share a sample file without sensitive data with me. This will make it easier for me to find the solution.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NL-Nicolas's avatar
      NL-Nicolas
      Helper I
      4 years ago

      Hey RicoZhou!

       

      Thanks for the response, it turns out my client's DataImport was only 4 months of data! So even if I have been getting the formula right this whole time there was no data to show! I will get back to this post soon after my call today to import the data. I appreciate you trying to help!

    • NL-Nicolas's avatar
      NL-Nicolas
      Helper I
      4 years ago

      Here is the error I received from that. I also tried 

      Last Week Sales = COUNTX(filter(DayfileSummary, DayfileSummary[NetSales]),DayfileSummary[Last Week])
      But it returns nothing! Really stumped on this one
  • NL-Nicolas's avatar
    NL-Nicolas
    Helper I
    4 years ago

    Update: My column with a date is a measured column. It seems it cannot be used however and my Data has been imported from SQL I feel like I have hit a wall on Power Bi functions here on accident. Help!