Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.