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 created_date for a particular date, the MTD and Today's sales numbers becomes equal which is obvious. Now, what I want is to create a temporary variable say temp and store the filtered date in it and then in my report I will just compare month and year of that date with the created_date and take data from 1st of a month to the date which has been selected. This should be done dynamically which means I should be able to input any date and the data should be generated as such :

 

Created date = 28-11-2016 (filter)

Today Sales = X (sales of 28-11-2016)

MTD Sales = Y (sales from 01-11-2016 to 28-11-2016)

 

How can I do this in Ppower BI.

 

4 Replies

  • 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