Actual for current year & TODAY

[oliviabaili]

Hi there,

In power Bi, I am creating a report that compares this years actual data to last years. For example, I have November data for 2022 but I need to calculate all of the data for November in 2021. 

Also I have a Data table, where calendar date is converted to fiscal date. 

Now I want to calculate this year actual sales amount. In formula I need to change TODAY(see below) to fiscal date (not calendar TODAY but Fiscal Today). 

SALES CURRENT ACTUAL = CALCULATE(SUM(SALES[SALES_Sales]), FILTER('Date',Date([Fiscal date].[Year]=YEAR(TODAY()))))

Can someone help me figure out a dax formula for it? Maybe using variables? 

Any help would be appreciated. 

[v-shex-msft]

Hi @oliviabaili,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[NanDeb · Accepted solution]

How are your sales and date tables related? 

Hoe would you like to display your results? 

I am assuming relationship is on Sales[Date]  = Date[Date] and you want something like a bar chart showing sales by fiscal year? 

So you need to filter the sales table based on the fiscal year which can be done in multiple ways depending on need but I would try a calculated column to the dimdate table that returns the fiscal year and/ or use  a virtual table in my measure depending on how I wanted to visualize the result 

Calculated Column in Date table

Fiscal year = YEAR('Date'[Fiscal Date] 

You should then be able to use a simple sales measure to calculate sales value 

Sales = SUM(Sales[Value])

Or if you want a more robust measure that validates the Fiscal year in the measure

Fiscal Year Sales = SUMX(SUMMARIZE('Date', 'Date'[Fiscal Year]), SUM(Sales[Value])

I am sure there are other approaches but this is the one I would try first