Forum Discussion

unnati's avatar
unnati
Frequent Visitor
9 years ago

Filter

 I have a sales table with created_date column and other transactional data of customers. In my reports, I want a particular's day sales, MTD sales and Target sales. whenever I apply a filter of crea...
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi unnati,

In power BI desktop, we are unable to create a temporary variable. You’d better add an date slicer to filter the data as follows. I try to reproduce your scenario using the following sample data.

 

First create Year, Month, Day column using the following formulas.

Year = YEAR(Sales[Date])
Month = MONTH(Sales[Date])
Day = DAY(Sales[Date])


Then create measure which will calculate the cumulative sum of sales for month to date.

 

MTD = CALCULATE(SUM(Sales[Sales]),FILTER(ALLEXCEPT(Sales,Sales[Month]),Sales[Date]<=MAX(Sales[Date])))

 

Finally, create three slicers including year, month and day separately. Create a table, select the Date, Sales and MTD as Value level. Like the following screenshots, when you select 2016/4/3, the highlighted in red will display the sale of 2016/4/3, and the highlighted in yellow will calculate the sum of 2016/4/1-2016/4/3.



If this is not what you want, please post the sample data or snapshot for further analysis.

Best Regards,
Angelia

unnati's avatar
unnati
Frequent Visitor
9 years ago

Thanks Angelia. I actually wanted to do do it for each month and the solution you have provided is doing cumulative for all months i.e adding 31st Jan to 1st Feb. I wanted it to be like do it up to 31st Jan and then if month(previous)!=month(current) then take it a sit is and after that from 2nd of Feb add 1st Feb value to it. But anyways, I got the solution of this problem.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi unnati,

    I am very happy you have resolved your problem, please share your solution or mark corresponding replay as answer.

    Best Regards,
    Aneglia

    • unnati's avatar
      unnati
      Frequent Visitor
      9 years ago

      I have taken the date filter for before dates, i.e. now the whole data set upto a selected date will be considered but I have written measure 

       

      for Today's quantity

       

      Units Sold=CALCULATE(COUNTROWS(tablename), FILTER(tablename,tablename[date] =MAX(tablename[date]))

       

      for MTD,

       

      MTD = CALCULATE(COUNTROWS(tablename), FILTER(tablename,MONTH(tablename[date])= MONTH(MAX(tablename[date]) &&YEAR('tablename[date])=YEAR(MAX(tablename[date]))&&DAY(tablename[date])>=1)