Forum Discussion

JWT's avatar
JWT
Frequent Visitor
4 years ago
Solved

Last Year Year To Date summarized

Hi, I am new to Power BI and struggling my way to find a way to calculate a total amount of sales YTD last year. I have a table containing sales and dates (incomplete) and another table containing ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi JWT ,

     

    Please try the following formula:

     

    Sales YTD LY =
IF (
    HASONEVALUE ( Periode[Måned] ),
    IF (
        [Sales YTD] <> BLANK (),
        CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Periode[Dato] ) )
    ),
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            Periode[Dato],
            EDATE ( MIN ( Periode[Dato] ), -12 ),
            EOMONTH ( MAX ( Sales[Date] ), -12 )
        )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

JWT's avatar
JWT
Frequent Visitor
4 years ago

Periode[dato] = complete date table marked as date table.

Periode[YTD] = column with only dates for this year until now (1/1/2022 - 6/1/2022 at the moment)

 

The matrix is only to show the values, I mainly need the total cards to be correct.

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi JWT ,

 

Please try the following formula:

 

Sales YTD LY =
IF (
    HASONEVALUE ( Periode[Måned] ),
    IF (
        [Sales YTD] <> BLANK (),
        CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Periode[Dato] ) )
    ),
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            Periode[Dato],
            EDATE ( MIN ( Periode[Dato] ), -12 ),
            EOMONTH ( MAX ( Sales[Date] ), -12 )
        )
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • JWT's avatar
    JWT
    Frequent Visitor
    4 years ago

    Hi Winniz

    Thank you for your reply. Based on your file, that does indeed look like a good solution!

     

    I have used an alternative, where the expression includes the filer eg:

     

    Sales YTD LY = var _YTDLYmin = DATE(YEAR(TODAY())-1,1,1) var _YTDLYmax = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) return CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),KEEPFILTERS(AND(Periode[Dato]>=_YTDLYmin,Periode[Dato]<=_YTDLYmax)))

     

    Thank you for you effort.

    /Jonatan