cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oliviabaili
New Member

Actual for current year & TODAY

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. 

1 ACCEPTED SOLUTION
NanDeb
Helper I
Helper I

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

 

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
NanDeb
Helper I
Helper I

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

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors