Budget previous year when no year is selected on filter

[Anonymous]

Hi,

I have a data that goes from 2008 to 2020 with real and Budget sales.

The client wants to see the current and last year budget while selects the year in a filter, like the image:

 

But the problem is when no year is selected; the image should show the sum of budget for all years and the budget for the previous year of the last year with data (not necessarily has to show budget for 2019). Am I explaining well?

I am using the following formula for previous year

BudgetPY = CALCULATE(SUM(FETS[BUDGET]);PREVIOUSYEAR(FETS[Year_Date]))

Then I tried to calculate the last year with data as the following:

YEAR_NOT_BLANK = CALCULATE(MAX(FETS[Year_Date]);(NOT(ISBLANK(FETS[BUDGET]))))

LASTYEAR_NOTBLANK = YEAR([YEAR_NOT_BLANK])-1

…and tried to use LASTYEAR_NOT_BLANK as a filter on the BudgetPY calculation but it does not work

Can enyone help me with this? Should be really simple

Many thanks

José

[Ashish_Mathur]

Hi @Anonymous,

Share the link from where i can download your file.

[Anonymous]

Hi, @Ashish_Mathur

Thanks for writing

The only thing I would like to have is MAX PREVIOUS YEAR when no filter is selected.

This DAX works (For MAX YEAR):

Budget = CALCULATE (

        SUM ( FETS[VALOR] );

        FILTER ( FETS; FETS[Year_Date] = MAX ( FETS[Year_Date] ) );NOT(ISBLANK(FETS[VALOR]))

    )

And for previous max year in trying to use

BudgetPY = CALCULATE (

        SUM ( FETS[VALOR] );

        FILTER ( FETS; FETS[Year_Date] = MAX ( FETS[Year_Date] )-1 );NOT(ISBLANK(FETS[VALOR]))

    )

Or

BudgetPY = CALCULATE (

        SUM ( FETS[VALOR] );

        FILTER ( FETS; FETS[Year_Date] = CALCULATE(MAX ( FETS[Year_Date] )-1 ));NOT(ISBLANK(FETS[VALOR]))

    )

And nothing is working. Can you help me please? What am I doing wrong?

Thank you very much

José

[Ashish_Mathur]

Hi,

It will help if you share some data and show the expected result.

[ImkeF]

This measure should work:

BudgetPY =
IF (
    ISFILTERED ( FETS[Year_Date] ),
    CALCULATE ( SUM ( FETS[BUDGET] ), PREVIOUSYEAR ( FETS[Year_Date] ) ),
    CALCULATE (
        SUM ( FETS[BUDGET] ),
        FILTER ( FETS, FETS[Year_Date] = MAX ( FETS[Year_Date] ) )
    )
)

But in general it is not a good practice to operate without a separate calendar table.

[Anonymous]

Thank you very much Imkef

But the problem is that the max year does not have data, it is to say, my date range goes from 2008 to 2020, but the max year with budget data is 2016. When no year is selected on the filter I need to show the budget for 2016 dynamically (the latest year with data).

Thank you very much 🙂

[ImkeF]

It seems that I have a wrong understanding of your data structure. The example you've given used just one table "FETS": How are you running a Dates until 2020 in there without budget data? Are you using a separate calendar-table? 

[Anonymous]

Hi, yes I have one table FETS. The thing is in that table I have other values on the same column than budget, actually that column is called "Values" but I used the name Budget on the question just to simplify. There is another column called "Description" indicating that until 2020 should be budget data but for the moment the value is in blank.

[ImkeF]

You might consider clean up your data before moving on..

Otherwise please post sample data.