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

[CC-BA]

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

    )

)

[Anonymous]

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

[CC-BA]

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 🙂

[Anonymous]

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

[CC-BA]

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!

[Anonymous]

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

[CC-BA]

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. 🙂

[Anonymous · Accepted solution]

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

[Poojara_D12]

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 

[CC-BA]

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. 🙂