Dynamic Previous and Current Total Sales Calculation

[Andrea_Jess]

Hi All,

I'm wanting to show monthly YTD changes in sales number. If no financial period slicer is selected, I want to show sales figures for the current month and sales figures from last month (i.e. Sept21 YTD vs. Aug21 YTD).

Where I'm getting stuck is, if a user selects FY20 in the slicer, how do I get my visual to show sales figures for May20 YTD and Jun20 YTD? i.e. if a prior financial year is selected, I want the current month value to show the last month of that financial year (i.e. June) and the prior month (i.e. May).

However, we want to also ensure that an end user can select any given month within a previous financial period. If the user selects FY20 April, then the 'Current Month' figure would be for April20 and 'Prior Month' would be March20.

Someone very helpful suggested this measure but for some reason i'm getting blanks. When i troubleshoot the visual doesn't render 😞 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"06/30"))

YTD Sales till last month = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"06/30") ,filter('Date',  'Date'[Date]  <= eomonth(max('Date'[Date]),-1) )  )

Any assistance would be appreciated.

[MFelix · Accepted solution]

Hi @Andrea_Jess ,

Without any data is difficult to give you the correct answer however believe that your measures are lacking the context  especially the last one try the following:

YTD Sales till last month =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    DATESYTD ( 'Date'[Date], "06/30" ),
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= EOMONTH ( MAX ( 'Date'[Date] ), -1 ) )
)

[v-yiruan-msft · Accepted solution]

Hi @Andrea_Jess ,

Please update the formula of measure [YTD Sales till last month] as below:

YTD Sales till last month = CALCULATE ( [YTD Sales], PREVIOUSMONTH ( 'Date'[Date] ) )

Alternatively, since we don't know which fields you have applied as slicer options, we can't give you the appropriate measure. You can refer to the following blog to determine if the slicer has any options selected and change your measure based on your scenario.

No slicer selected — No chart in Power BI

If the above ones can't help you find the solution, please provide more details(sample data, slicer setting or your sample pbix file etc.) for your requirement. Thank you.

Best Regards

[MFelix · Accepted solution]

Hi @Andrea_Jess ,

Without any data is difficult to give you the correct answer however believe that your measures are lacking the context  especially the last one try the following:

YTD Sales till last month =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    DATESYTD ( 'Date'[Date], "06/30" ),
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= EOMONTH ( MAX ( 'Date'[Date] ), -1 ) )
)