Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Frustrated - YTD Measures Not working

Having a funky issue with the YTD/PYTD measures. Hoping someone can help me with this.

 

I have a Total Sales Amount measure: 

Total Sales Amount = SUM(SalesData[EXTENDED PRICE])

A PYTD measure: 

Total Sales LYTD = VAR EndDate = DATE( YEAR( TODAY() ) -1, MONTH( TODAY() ), DAY( TODAY() ) )

VAR StartDate = FIRSTDATE( SAMEPERIODLASTYEAR( Dates[Date] ) )

VAR SalesLY = CALCULATE(

[Total Sales Amount],

DATESBETWEEN( Dates[Date], StartDate, EndDate ) )

RETURN

IF( ISBLANK( SalesLY ), BLANK(), SalesLY )

And a Sales 2yrs ago measure: 

sales 2yrs ago = VAR EndDate = DATE( YEAR( TODAY() ) -2, MONTH( TODAY() ), DAY( TODAY() ) )

VAR StartDate = FIRSTDATE( SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR( Dates[Date] ) ))

VAR Sales2yrs = CALCULATE(

[Total Sales Amount],

DATESBETWEEN( Dates[Date], StartDate, EndDate ) )

RETURN

IF( ISBLANK( Sales2yrs ), BLANK(), Sales2yrs )

These measures should be showing the total sales for each day with a total to date at the bottom. However, the PYTD and Sales 2 yrs ago measures are showing like a reverse running total. So the total sales to date is shown at the beginning date (1/1/19) and the initial total sales amount is shown on today's date (05/30/19)

 

 

I'm very new to Power BI and Dax so the formulas I used were suggestions made to me by others. When I noticed this problem, I scrapped it all and started over following this tutorial: https://www.youtube.com/watch?v=zYIxukD2KCM&t=280s

 

But ended up with another weird issue where each colum (YTD, PYTD, etc) were just running totals and if I placed them in data tables, no total was calculated at the bottom. The space for the total was there... but no number populated. 

 

I'm really confused. Is there anyone who can help me? I just need to be able to show 

  • Current YTD numbers (total 2019 numbers)
  • PYTD numbers (total 2018 numbers up to today's date in 2018) 
  • 2Y PYTD numbers (total 2017 numbers up to today's date in 2017) 

 

 

 

 

 

Need Help
Tutorial Requests

13 Replies

  • calerof's avatar
    calerof
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    Hi Anonymous ,

     

    Please try to use the following measures:

    Sales Total = SUM( Sales[Sales] )
    Sales YTD = IF(
                MIN( 'Calendar'[Date] ) <= CALCULATE( MAX( Sales[Date] ), ALL( Sales ) ),
                        CALCULATE(
                                Sales[Sales Total],
                                DATESYTD( 'Calendar'[Date] )
                        )
                    )
    Sales YTD LY = VAR DataMaxDate = 
                                CALCULATE( MAX( Sales[Date] ), ALL( Sales ) )
                      RETURN
                                CALCULATE(
                                        Sales[Sales YTD],
                                        SAMEPERIODLASTYEAR(
                                            INTERSECT(
                                                VALUES( 'Calendar'[Date] ),
                                                DATESBETWEEN( 'Calendar'[Date], BLANK(), DataMaxDate )
                                            )
                                        )
                                )

    Supposing you have the following data sample:

    Sales data sample

    You'd have the following information:

     

    Here's my pbix pbix

     

    Hope it helps!

     

    Regards,

     

    Fernando

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      calerof Thank you for this. Unfortunately, this is still showing a running total and the numbers seem to be a little off: 

       

       

      Is there a way to see what the total sales were for that day instead of seeing a running total like this? 

      Or even total sales per month instead of a running total? 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Here is why this is important to me: 

        When I translate these into visuals, the total sales shows sales per month... not a cumulative sales amount over those months. However, the other measures all show the cumulative amount which skews the graphic.