Solved: Re: Previous Year not working

Anonymous · ‎06-23-2021

Hi,

I am running a previous year measure with

Sales Prev Year = CALCULATE(SUM('Chalgrove FactSalesOrderLine'[SaleValueBase]),PREVIOUSYEAR(DimDate[FullDate].[Year]))

However I am getting the following error

Calculation error in measure 'Chalgrove FactSalesOrder'[Sales Prev Year]: A column specified in the call to function 'PREVIOUSYEAR' is not of type DATE. This is not supported.

The fulldate column is a date type..

Can anyone help?

Thanks

Chris

CNENFRNL · ‎06-23-2021

Sales Prev Year =
CALCULATE(
    SUM( 'Chalgrove FactSalesOrderLine'[SaleValueBase] ),
    PREVIOUSYEAR( DimDate[FullDate] )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

CNENFRNL · ‎06-23-2021

Sales Prev Year =
CALCULATE(
    SUM( 'Chalgrove FactSalesOrderLine'[SaleValueBase] ),
    PREVIOUSYEAR( DimDate[FullDate] )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous · ‎06-23-2021

It is not failing now.. however it is not returning any values.

CNENFRNL · ‎06-23-2021

No surprise; any measure evalutes in a comprehensive evaluation context. I helped correct syntactic error of DAX; analyse and author measures for desired results by yourself.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous · ‎06-23-2021

Lol thanks for the help.. at least its not failing now .. much appreciated.

ERD · ‎06-23-2021

Hi @Anonymous ,

Check that DimDate[FullDate] is of Date type and try

Sales Prev Year = CALCULATE(SUM('Chalgrove FactSalesOrderLine'[SaleValueBase]),PREVIOUSYEAR(DimDate[FullDate]))

See https://dax.guide/previousyear/

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Stand with Ukraine!

Here is an official way you can support Ukraine financially:
Come Back Alive foundation: https://savelife.in.ua/en/
Thank you!

amitchandak · ‎06-23-2021

@Anonymous ,Please mark this table as date tbale DimDate, also check data type of join column in FactSalesOrder, that should also be date column

if not

Can you share a sample pbix after removing sensitive data.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Anonymous · ‎06-23-2021

Hi @amitchandak

I am unable to make it a dat table as there is missing dates. our system puts defasult dates starting at 1900 so we need to have that in there along with the other info.

Thanks