Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Member
Posts: 92
Registered: ‎05-25-2016
Only show current month and next two and compare with same period last year
[ Edited ]

Hi,

 

I have a sales table with a column for the date and another one for sales amount.

 

I want to show only sales of current month and two months after and compare with sales same period last year.

 

The filter for the current month must change automatically without user interaction.

 

To do that I have created 5 columns in sales table:

   - Beginning date current month

   - Last day two months later

   - Beginning date current month last year

   - Last day two months later last year

   - Conditional column that returns true if (date >= beginning date current month and <= last day two months later) OR (date >= beginning date current most last year and <= last day two months later last year).

 

Does anyone knows if there is an easier way to do that?

 

Thanks,

 

 

 

Frequent Visitor
Posts: 14
Registered: ‎07-12-2015
Re: Only show current month and next two and compare with same period last year

You can use TODAY() function to get the current date. 

From ther you can calculate current month.

Then you use DATEADD() to calculate next two months.

Hope that helps.

Member
Posts: 92
Registered: ‎05-25-2016
Re: Only show current month and next two and compare with same period last year

Thank you, @soheil

 

I have done it the way I told you and it works nearly ok.

 

If I use a matrix I don't have any problem to compare years

  - Rows for years.

  - Column for month and day.

  - Values are total amount

 

The problem is when I want to represent that in a chart. I would like to use line chart with two lines:

    -  One line for the sales current month and two months later (date by date).

    -  The Other line must shows sales last year current month and two months later (date by date).

 

Thanks & Greetings

New Contributor
Posts: 655
Registered: ‎07-28-2016
Re: Only show current month and next two and compare with same period last year

Not sure if you can adapt this for your use case, but it sounds like it might help.  Maybe not, but worth a look.

 

https://blog.oraylis.de/2016/02/relative-week-column-with-dax-power-query/

Member
Posts: 92
Registered: ‎05-25-2016
Re: Only show current month and next two and compare with same period last year
Thank you, @dkay84_PowerBI

Actually, i hace to do another reportó showing sales amont last 4 weeks and week by week.

So, The article you refer to is perfect for this other case.

It seems I can do something similar with months. I hace to deep in little more.

Anyway, It's a very interesting post

As i have said before, many thanks!!
Frequent Visitor
Posts: 6
Registered: ‎07-22-2016
Re: Only show current month and next two and compare with same period last year

Angel,

 

This may apply to your question or not.

I use this code to display the value of the last data point in a line chart in a Card visual above the chart since I don't want to show the values for all the data points.

 

CurDateRngeVar =
VAR LstMthV = CALCULATE(LASTNONBLANK(Nursing_Data[Period Month],1))
VAR LstYrV = 2016
RETURN
CALCULATE(LASTNONBLANK(Nursing_Data[Data Range],1),
 FILTER(Nursing_Data,
 (MIN(Nursing_Data[Period Year]) && Nursing_Data[Period Month]=LstMthV))

Highlighted
Member
Posts: 92
Registered: ‎05-25-2016
Re: Only show current month and next two and compare with same period last year

Thanks, everybody

 

It seems I have solved the issue. I have created three columns:

   - Beginning date actual month = DATE(YEAR(TODAY());MONTH(TODAY());"01")

   - Last date two months later = EOMONTH(TODAY();2)

   - Data in three months period  = IF(Sales[Date]>=Sales[Beginning date actual month] && Sales[Date]<=Sales[Beginning date two months later];TRUE();FALSE()) 

 

I have filtered report by last column to show only sales in three months time (one line of the chart).

 

To draw another line for same period last year. I have created this measure:

    - Sales three months period last year = CALCULATE(SUM(Sales[Sales Amount]);SAMEPERIODLASTYEAR(Calendar[Date]))

 

Calendar is a date table.

 

Hope it helps,

 

LinkedId: https://es.linkedin.com/in/angel-luis-correa-martín-04a8402b