topic Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend in DAX Commands and Tips

Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

CC-BA — Thu, 05 Dec 2024 00:13:49 GMT

Hi!

I am struggling with a dax to measure daily average spend for previous fiscal years and fiscal year-to-date. Our fiscal runs April 1 to March 31. The dax below is calculating all the previous years correctly, but for some reason it is using 365 days to calculate the fiscal year-to-date when it should be using 248 days (calculating Apr 1 to Dec 4). I have a date calendar setup properly (marked as a date table), I can calculate days fiscal YTD properly and daily average spend fiscal YTD by itself; so I am unsure what the issue is. Please help!! 🙂

Daily Average Spend (Fiscal vs YTD) =

IF(

HASONEVALUE('FiscalCalendar'[Fiscal Year]), -- Check if Fiscal Year is in context

// Calculate Daily Average Spend for the entire Fiscal Year

DIVIDE(

CALCULATE(

SUM('Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend]),

'FiscalCalendar'[Fiscal Year] = VALUES('FiscalCalendar'[Fiscal Year]) -- Restrict to the selected Fiscal Year

CALCULATE(

DISTINCTCOUNT('FiscalCalendar'[Date]),

'FiscalCalendar'[Fiscal Year] = VALUES('FiscalCalendar'[Fiscal Year]) -- Ensure we're counting days only in the selected fiscal year

)

// Calculate Daily Average Spend for Fiscal YTD (April 1 to Today)

DIVIDE(

CALCULATE(

SUM('Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend]),

'FiscalCalendar'[Date] >= DATE(YEAR(TODAY()), 4, 1) && 'FiscalCalendar'[Date] <= TODAY() -- Filter for fiscal year from April 1 to today

[DaysInFiscalYear] -- Use the DaysInFiscalYear measure for the correct number of days

)

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

Poojara_D12 — Thu, 05 Dec 2024 06:22:45 GMT

Hi CC-BA

Define a measure for Fiscal Year-to-Date days:

DaysFiscalYTD = CALCULATE( COUNTROWS('FiscalCalendar'), 'FiscalCalendar'[Date] >= DATE(YEAR(TODAY()), 4, 1) && 'FiscalCalendar'[Date] <= TODAY() )

Fix the Main Measure:
Replace [DaysInFiscalYear] with the correct Fiscal YTD days:

Daily Average Spend (Fiscal vs YTD) = IF( HASONEVALUE('FiscalCalendar'[Fiscal Year]), DIVIDE( CALCULATE( SUM('Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend]), 'FiscalCalendar'[Fiscal Year] = VALUES('FiscalCalendar'[Fiscal Year]) ), CALCULATE( DISTINCTCOUNT('FiscalCalendar'[Date]), 'FiscalCalendar'[Fiscal Year] = VALUES('FiscalCalendar'[Fiscal Year]) ) ), DIVIDE( CALCULATE( SUM('Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend]), 'FiscalCalendar'[Date] >= DATE(YEAR(TODAY()), 4, 1) && 'FiscalCalendar'[Date] <= TODAY() ), [DaysFiscalYTD] -- Correct Fiscal YTD days measure ) )

Key Fix:
The measure DaysFiscalYTD ensures only the fiscal year-to-date days (248 in your case) are used in the calculation, resolving the issue.

Please check the above and let me know did it solved your issue.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

Anonymous — Thu, 05 Dec 2024 06:24:21 GMT

Hi CC-BA ,

Please update the formula of your measure [Daily Average Spend (Fiscal vs YTD)] as below and check if it can return the expected result...

Daily Average Spend (Fiscal vs YTD) = VAR _tab = FILTER ( 'FiscalCalendar', 'FiscalCalendar'[Date] >= DATE ( YEAR ( TODAY () ), 4, 1 ) && 'FiscalCalendar'[Date] <= TODAY () ) RETURN IF ( HASONEVALUE ( 'FiscalCalendar'[Fiscal Year] ), -- Check if Fiscal Year is in context // Calculate Daily Average Spend for the entire Fiscal Year DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), 'FiscalCalendar'[Fiscal Year] = VALUES ( 'FiscalCalendar'[Fiscal Year] ) -- Restrict to the selected Fiscal Year ), CALCULATE ( DISTINCTCOUNT ( 'FiscalCalendar'[Date] ), 'FiscalCalendar'[Fiscal Year] = VALUES ( 'FiscalCalendar'[Fiscal Year] ) -- Ensure we're counting days only in the selected fiscal year ) ), // Calculate Daily Average Spend for Fiscal YTD (April 1 to Today) DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), _tab ), COUNTROWS ( _tab ) ) )

If the above one can't help you figure out, please provide some raw data in your table 'Invoice Details Feb 29, 2020 - Nov 19, 2024' and 'FiscalCalendar' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

CC-BA — Thu, 05 Dec 2024 18:23:02 GMT

Hi Anonymous,

Thank you for your response and for your help! Unfortunately the dax didn't produce the correct results. I cannot provide a sample set of the data as it is a massive dataset that is confidential. I can provide you screenshots of my table format and relationships to give you an idea. Hope this helps you!!

Here's the Fiscal Calendar table :

Dax to create Fiscal Calendar:

FiscalCalendar =

ADDCOLUMNS(

CALENDAR(DATE(2020, 2, 1), DATE(2028, 03, 31)), -- Adjust date range as needed

"FiscalYear",

IF(

MONTH([Date]) >= 4,

YEAR([Date]), -- If month >= 4, fiscal year is the current year

YEAR([Date]) - 1 -- If month < 4, fiscal year is the previous year

"FiscalQuarter",

SWITCH(

TRUE(),

MONTH([Date]) >= 4 && MONTH([Date]) <= 6, 1, -- April to June -> Q1

MONTH([Date]) >= 7 && MONTH([Date]) <= 9, 2, -- July to September -> Q2

MONTH([Date]) >= 10 && MONTH([Date]) <= 12, 3, -- October to December -> Q3

MONTH([Date]) >= 1 && MONTH([Date]) <= 3, 4 -- January to March -> Q4

"FiscalQuarterSortOrder",

SWITCH(

TRUE(),

MONTH([Date]) >= 4 && MONTH([Date]) <= 6, 1, -- Q1: April to June

MONTH([Date]) >= 7 && MONTH([Date]) <= 9, 2, -- Q2: July to September

MONTH([Date]) >= 10 && MONTH([Date]) <= 12, 3, -- Q3: October to December

MONTH([Date]) >= 1 && MONTH([Date]) <= 3, 4 -- Q4: January to March

"FiscalMonth",

MOD(MONTH([Date]) - 4, 12) + 1, -- Shift months: April = 1, May = 2, ..., March = 12

"FiscalMonthName",

SWITCH(

MOD(MONTH([Date]) - 4, 12) + 1,

1, "April",

2, "May",

3, "June",

4, "July",

5, "August",

6, "September",

7, "October",

8, "November",

9, "December",

10, "January",

11, "February",

12, "March"

)

Invoice Details Feb 29, 2020 - Nov 19, 2024 Table Columns:

Relationship:

Thanks so much 🙂

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

CC-BA — Thu, 05 Dec 2024 18:26:26 GMT

Thanks for your help Poojara_D12, unfortunately it did not produce the correct results. 😞 I replied to the previous poster with more information on my tables. Maybe it could help you? Appreciate any suggestions. 🙂

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

Anonymous — Fri, 06 Dec 2024 06:36:30 GMT

Hi CC-BA ,

I created a sample pbix file(see the attachment), it update the formula of measure as below. Later check if it can return the expected result...

Daily Average Spend (Fiscal vs YTD) = IF ( HASONEVALUE ( 'FiscalCalendar'[Fiscal Year] ), -- Check if Fiscal Year is in context // Calculate Daily Average Spend for the entire Fiscal Year DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), 'FiscalCalendar'[Fiscal Year] = VALUES ( 'FiscalCalendar'[Fiscal Year] ) -- Restrict to the selected Fiscal Year ), CALCULATE ( DISTINCTCOUNT ( 'FiscalCalendar'[Date] ), 'FiscalCalendar'[Fiscal Year] = VALUES ( 'FiscalCalendar'[Fiscal Year] ) -- Ensure we're counting days only in the selected fiscal year ) ), // Calculate Daily Average Spend for Fiscal YTD (April 1 to Today) DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), 'FiscalCalendar'[Date] >= DATE ( YEAR ( TODAY () ), 4, 1 ) && 'FiscalCalendar'[Date] <= TODAY () ), CALCULATE ( COUNT ( 'FiscalCalendar'[Date] ), 'FiscalCalendar'[Date] >= DATE ( YEAR ( TODAY () ), 4, 1 ) && 'FiscalCalendar'[Date] <= TODAY () ) ) )

If the above one can't help you, could you please provide the correct result base on my sample data? It would be helpful to find the solution. Thank you.

Best Regards

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

CC-BA — Wed, 11 Dec 2024 17:06:49 GMT

Hi,

Thanks for your reply but this solution does not work. I reviewed your file and the 2024 result of 4.53 Daily Average Spend is incorrect, it should be 6.49 as it should be calculating off of 255 days (Apr 1-Dec 11 is 255 days), instead it is dividing by 365 days which is resulting in 4.53. Is there no way to have a table or a line graph to display the daily average spend for all previous fiscal years and the fiscal YTD for the current fiscal year? The only way I have been able to find a workaround to produce the correct results is by adjusting my fiscal calendar to the current date (ex. today is Dec 11, so I change it to today). Thanks!

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

Anonymous — Thu, 12 Dec 2024 03:11:17 GMT

Hi CC-BA ,

I updated my sample pbix file(see the attachment), please check if it can help you get the expected result.

1. Remove the relationship between the fact table and fiscal calendar table

2. Update the formula of measure [Daily Average Spend (Fiscal vs YTD)] as below

Daily Average Spend (Fiscal vs YTD) = VAR _fiscalyear = SELECTEDVALUE ( 'FiscalCalendar'[Fiscal Year] ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), FILTER ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024', 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Payment Date] >= DATE ( _fiscalyear, 4, 1 ) && 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Payment Date] <= DATE ( _fiscalyear + 1, 3, 31 ) ) ), COUNTROWS ( FILTER ( ALLSELECTED ( 'FiscalCalendar' ), 'FiscalCalendar'[Fiscal Year] = _fiscalyear && 'FiscalCalendar'[Date] <= TODAY () ) ) )

Best Regards

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

CC-BA — Thu, 12 Dec 2024 15:42:42 GMT

Thanks, that does produce the correct results in your sample file, but if I remove the relationship between my fiscal calendar and the fact table, then my other time-based aggregations, measures etc. that are included in my dashboard will not work. Thanks so much for all of your assistance. 🙂

Re: Dax help needed - Measure for Daily Average Fiscal Year Spend AND Fiscal YTD Spend

Anonymous — Fri, 13 Dec 2024 09:14:29 GMT

Hi CC-BA ,

You can follow the steps below to get the expected result:

1. Keep the relationship between the table 'FiscalCalendar' and fact table

2. Create another new date table(DO NOT create any relationship with your fact table)

3. Change the date field of above line chart with the date field of this new created date table

Or still keep the relationship between fiscal calendar table and fact table, then update the formula of measure as below:

Daily Average Spend (Fiscal vs YTD) = VAR _fiscalyear = SELECTEDVALUE ( 'FiscalCalendar'[Fiscal Year] ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Spend] ), 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Payment Date] >= DATE ( _fiscalyear, 4, 1 ), 'Invoice Details Feb 29, 2020 - Nov 19, 2024'[Payment Date] <= DATE ( _fiscalyear + 1, 3, 31 ) ), COUNTROWS ( FILTER ( ALLSELECTED ( 'FiscalCalendar' ), 'FiscalCalendar'[Fiscal Year] = _fiscalyear && 'FiscalCalendar'[Date] <= TODAY () ) ) )

Best Regards