Filter

[unnati]

 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.

[v-huizhn-msft]

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]

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]

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]

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)