cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

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.

cumulative total.PNG

 

 

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

here is the pbi.file

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

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

@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"))

 

Capture.PNG

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

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

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...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Resident Rockstar
Resident Rockstar

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

Hi @Bryan75,

 

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.

 

Capture.PNG

 

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

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User IV
Super User IV

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

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Resident Rockstar
Resident Rockstar

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

Hi @Bryan75

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

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

@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"))

 

Capture.PNG

View solution in original post

Highlighted
Advocate I
Advocate I

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors