How to calculate the sales total with changed date range based on the current filters

[j_w]

The Report has a Sales table with Date, Product, Amount$ fields, also have two filters, Date and Product matching the same name fields.

The financial year end date is 31 March.

How to write a measure to calculate the year-to-date total amount$ based on the current two filters?

For example:

Case 1

If the user selected Date filter range from 2020-09-01 to 2020-07-14,

then the year-to-date total amount$ will be from 2020-04-01 to 2020-07-14.

Case 2

If the user selected Date filter range from 2020-02-01 to 2020-08-31, with Product filter Product01 selected

then the year-to-date total amount$ will be from 2019-04-01 to 2020-08-31 for Product01.

Notes: in case 2, since the user selected Date filter range covers two financial years, so the year-to-date should also cover two financial years from 2019-04-01 to the max date of the user selected date range 2020-08-31.

[v-zhenbw-msft · Accepted solution]

Hi @j_w ,

We can create a FY date table that has now relationship with the Sales table and a measure to meet your requirement.

1. Create a date table and add two columns.

Date table = CALENDARAUTO()

FY = 
var _FY = if(month([Date])<=3,year([Date])-1,year([Date]))
return
_FY

Star FY = DATE('Date table'[FY],4,1)

2. Then we can create a measure and use the Date table [Date] to be a slicer.

Measure =
VAR _min_date =
    MIN ( 'Date table'[Date] )
VAR _max_date =
    MAX ( 'Date table'[Date] )
VAR _currentFYmonth =
    CALCULATE (
        MAX ( 'Date table'[Star FY] ),
        FILTER ( 'Date table', 'Date table'[Date] = _min_date )
    )
VAR _result_date =
    IF (
        _min_date > _currentFYmonth,
        _currentFYmonth,
        DATE ( YEAR ( _currentFYmonth ) - 1, MONTH ( _currentFYmonth ), 1 )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] >= _result_date
                && 'Table'[Date] <= _max_date
        )
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[amitchandak]

@j_w ,

You can use start of year with end date of year given in function. Or you can start of year from you table . Use date table as you need Continuous dates for this

Measure =
var _min = minx(allselected(Date),startofyear([Date[Date],"3/31")) //or use start of year from calendar
var _max = maxx(allselected(Date),[Date[Date])
return
calculate([measure], filter(all(Date), Date[DAte]<=_max && Date[DAte]>=_min))

FY of your choice - https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[amitchandak]

@j_w ,

Try like

Measure =
var _min = minx(allselected(Date),startofyear([Date[Date],"3/31")) // take start of year from you date table
var _max = maxx(allselected(Date),[Date[Date])
return
calculate([measure], filter(all(Date), Date[DAte]<=_max && Date[DAte]>=_min))

Make sure you use a date table with continuous date

[CNENFRNL]

Hi, @j_w , you can assign a specific date as year_end_date in TOTALYTD func, say "3/31" for your case,

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

 Official doc for more details,

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

[AllisonKennedy]

Not sure about your Case 2 example, should that only work if they select a date between 2019-04-01 and 2020-03-31?

See if this helps: https://blog.enterprisedna.co/learn-how-to-calculate-sales-financial-year-to-date/

It's one way to use financial YTD calculations by using the built in time intelligence functions in Power BI with Year End date specified. You must have a DimDate table for this to work properly. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[j_w]

@AllisonKennedy  I have added some explanation:

In case 2, since the user selected Date filter range covers two financial years, so the year-to-date should also cover two financial years from 2019-04-01 to the max date of the user selected date range 2020-08-31.