Sales Actuals for Month and then a YTD for the Total Column

[SammiP]

Hi Community.  Please help

I want to have Columns from March - Feb.  Each Month Must Display the Actual Sales for that Month (NOT TOTAL YTD), then the total column must give YTD.  If the user selects April in the month Slicer.  The April column will Have April's Data (NOT CUMULATIVE OR YTD) and in the Total Column the YTD from Mar .

Eg. 1

Mar  Apr  May  YTD

10    20     30     60

Eg. 2If I select April from the Month Slicer

Mar  Apr  YTD

10    20     30    

See OutCome Hoped.

[v-kelly-msft · Accepted solution]

Hi @SammiP ，

First create a dim date table as below:

Date = VALUES('Table'[Date])

Then create a measure as below:

Measure =
IF (
    ISFILTERED ( 'Date'[Date] ),
    IF (
        ISINSCOPE ( 'Table'[Date] ),
        IF (
            MAX ( 'Table'[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
            ),
            BLANK ()
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] ) )
        )
    ),
    SUM ( 'Table'[Value] )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[vanessafvg]

have you created a file yet?   If you import your table in and filter by that column it should do that automatically.  Where are you struggling exactly?