Calculating Revenues of sales whith the same value of a column of TODAY()

[Anonymous]

Hey everyone, 

I am pretty newbie in PBI and there's something I know possible but having technical issues performing. 

I have two tables: 

1: Sales (sales amounts , sales info, full date - Wednesday, February 1, 2017 for example)

2: Date Table ( full date, year, month,.....,FY - which starts at October and I create using simple M formula,...etc)

two tables are connected through the Full Date Column

I am currently trying to create a Fiscal Year To Date measure which is dynamic in time using the Today() Function.

My thinking was summing all of the sales amounts having the same FY as Today( ) function has... however nothing really works for me.

I tried:     Calculate( Sum('Sales'[Sales Amount]), Filter( All('Date Table'),'Date Table'[FY]=Today( )))

I know this is an error but how do I get to filter all of the rows who has FY value equal to Today ( ) FY value? 

Pretty lost here...

Thank you all in advance 🙂

[v-danhe-msft]

Hi @Anonymous ,

Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[Anonymous]

I have 2 tables: Date Dim Table and Sales Fact Table ( I have more but they are irrelevant as for this questions) : 
Date Dim Sample:

Date	Year	QuarterOfYear	MonthOfYear	DayOfMonth	WeekEnding	Week Number	QuarternYear	ShortYear	FY	FY Quarter	ShortMonthName
Saturday, July 1, 2017	2017	3	7	1	Saturday, July 1, 2017	26	20170300	17	FY17	1	Jul
Sunday, July 2, 2017	2017	3	7	2	Saturday, July 8, 2017	27	20170300	17	FY17	1	Jul

Sales FactT Sample:

Month	Category	Brand	Sub Brand	Barcode (EA)	Industry	Sub Industry	Total Sales (K)	Month-Year as Date
Aug	Cat1	Pampers	X	454545	4th Chain	4ch	22222	Wednesday, August 1, 2018
Aug	Cat2	Pampers	Y	464646	4th Chain	4ch	33333	Wednesday, August 1, 2018

We calculate FY differently, although there's a destined function I want to be able to sum/avg all the sales having the same FY value as today as Today( ) - for the example let's Today( ) would give "Sunday, July 2, 2017" 
which FY value is "FY17":  so basically I want to create a measure which will track and sum all of the sales in FY17 (I already though of the possibility which the measure/calc column will also take in count future sales beyond today and made it so the Date Dim table will filter all of the rows beyond today so such possibility is prevented).

In general I don't know how to filter rows of a table based on a specific row's column value (e.g FY17 but could have been any other column..).

I hope this explanation in enough informative. 

Thanks again in advance 🙂 

[Anonymous]

@Anonymous - 

If I understand correctly, you need to do something like the following:

Yearly Sales = CALCULATE(           //Changes filters
	SUM('Sales'[SalesAmount]),  //Your Calculation
	ALL('Date'),                //Includes all of the Date table
        VALUES('Date'[Year])        //Includes only the year(s) associated with the "cell" being populated
)

[Anonymous]

Thanks for replying, however where is the reference to the FY17? 

lets say today( ) function gives us a row such as below: 

Date	Year	QuarterOfYear	MonthOfYear	DayOfMonth	WeekEnding	Week Number	QuarternYear	ShortYear	FY	FY Quarter	ShortMonthName
Saturday, July 1, 2017	2017	3	7	1	Saturday, July 1, 2017	26	20170300	17	FY17	1	Jul

I would like that Power BI look at the FY Value of today (for this specific example it is FY17) and sum all of the sales in the Sales Fact Table (each row represents a different sale as presented in the example at the original post) with a date that have also FY17 under the FY column.. ( Sum all revenues which FY = Today( ) .FY which doesn't exist in DAX from what I've seen so far).

Thanks again 

[Anonymous]

@Anonymous - 

In the example I provided, you'll need to substitute the following with your values.

Specifically:

'Sales' -> Name of Fact table

[SalesAmount] -> Name of Fact table column

'Date' -> Name of Date table

[Year] -> Name of Date table column. This will be [FY]