Cumulative total per year from 1rst January until 6th February

[Anonymous]

Hi

Let's assume that we are on 06/02/2018 and we have the sales from 01/01/2017 until 06/02/2018.
I would like to compare on a matrix the turnover done each year from 01/01 until 06/02.
It has to be a matrix to drill down per store.

By default, on February 2017, it includes sales from 01/02/2017 until 28/01/2017.

I tried to create a calendar table where I added SAMEPERIODLASTYEAR but it doesnt work.
Any solution please ?

here is the pbi.file

[v-piga-msft]

Hi @Anonymous，

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards

Cherry

[Ashish_Mathur]

Hi,

Sales from Feb 1 to Feb 6 2017 is 167.1.  You may download my PBI file from here.  I have also solved a similar problem here - Show sales only for corresponding months in prior years.

Hope this helps.

[Anonymous · Accepted solution]

@Ashish_Mathur

I ve been inspired by your solution.

The aim was to use a slicer with a range.

Therefore, I used a very very very primitve solution (i.e ugly and uncomfy for final user but effective).

Please find here the file

The cons: It's not dynamic. The user has to figure out about the date limit to get the YTD and to conceptualize it (1rst February => 0201)

I created a column which concatenate Month & Day on Query Editor.

= Table.AddColumn(#"Source", "MMDD", each Date.ToText([Date], "MM")& Date.ToText([Date], "dd"))

[v-piga-msft]

Hi @Anonymous,

What about the measure below?

Measure =
VAR year =
    MAX ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Turnover] ),
        DATESBETWEEN ( 'Calendar'[Date], DATE ( year, 1, 1 ), DATE ( year, 2, 6 ) ),
        FILTER ( 'Calendar', YEAR ( 'Calendar'[Date] ) = year )
    )

Here is the output.

If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous]

Unfortunately, your solution forces the user to verify the DAX formula and to refresh it at each update 😞

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...