Show number one month before , same month last year, same period year to that month

[paris]

Hi All,

I would like to show value by month by city

(1)  one month before

(2) same month last year

(3) same period year to that month and restart again new year start (ie. if it is Jun  2020, I like to sum from Jan to Jun 2020)

 

All the best,

Paris

[AllisonKennedy · Accepted solution]

A few things:
1) You may need to create a DateKey column in your Data1 table
2) Make sure to relate your Data1[DateKey] to DimDate[DateKey]
3) Use the DimDate[Date] or DimDate[Year] and DimDate[Month] in the report visualization. It looks like you're using Data1[Date] and that won't work with the time intelligence DATEADD

Let me know if that helps! 🙂

[Ashish_Mathur · Accepted solution]

Hi,

Try this:

1. Create a Calendar Table and write calculated column formulas in that table to extract Year, Month name and Month number
2. In the Calendar Table, sort the Month name column by the Month number column
3. Create a relationship from the Date column of your Data Table to the Date column of your Calendar Table
4. To your visual, drag the Year and Month column to your visual
5. Write these measures:

Total volume = SUM(Data[volume])

Total volume in previous month = calculate([total volume],previousmonth(calendar[date]))

Total volume YTD = calculate([Total volume],datesytd(calendar[date],"31/12"))

Total volume YTD last year = calculate([Total volume YTD],sameperiodlastyear(calendar[date]))

Hope this helps.

[paris]

Ashish,

Thank you very much for your reply. 
I combined Allison and yours and it worked well. (sameperiodlastyear did not work in my sheet but the rest worked)

All the best,

Paris

[Ashish_Mathur]

You are welcome.

[AllisonKennedy]

Hi Paris~

Do you have a DimDate table for this report?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

If so, you can use;

PreviousMonth = CALCULATE( SUM(Table[Volumne]), DATEADD(DimDate[Date], -1, Month))

Year to date this year = CALCULATE( SUM(Table[Volume]), DATESYTD(DimDate[Date]))

YTD Last Year = CALCULATE( [Year to date this year] , DATEADD(DimDate[Date], -12, Month))

[paris]

Hello Allison,

Thank you for your reply.

I newly created DimDate from your link and tried PreviousMonth. 

I got same month valuefor same city. but not previous month value. Is there anything I should check?

Thanks again. 

All the best,

Paris

[AllisonKennedy]

Can you share a sample file or a couple screenshots?

Are you using the DimDate[Date] in the visual? Have you created PreviousMonth as a MEASURE (not a column)?

[paris]

Hi Allison,

Thank you very much for your reply. I tried both in column and measure and got the same result.

(I am using visual for measure)

Unfotunately, I am not able to share screen shot due to confidentiality. 

Is there anyway I can do , perhaps without using DimDate?

Thank you again for your help. 

All the best,

Paris

[AllisonKennedy]

Hi Paris~
It should work, so maybe if you can try using the sample data table from your post as the data source and upload that pbix file to OneDrive so we can see the problem?
I can't help troubleshoot without more information on exactly what you have done and what the data structure looks like.
Thanks!

[paris]

Hi Allison,

Thank you so much for your reply again.

I set up DimDate and mark as date table and use your formula in measure. 

I tried it with my sample data and here is my screen shot.  Hope it helps to determin my error?

Thank you again.

All the best,

Paris

[AllisonKennedy · Accepted solution]

A few things:
1) You may need to create a DateKey column in your Data1 table
2) Make sure to relate your Data1[DateKey] to DimDate[DateKey]
3) Use the DimDate[Date] or DimDate[Year] and DimDate[Month] in the report visualization. It looks like you're using Data1[Date] and that won't work with the time intelligence DATEADD

Let me know if that helps! 🙂

[paris]

Hi Allison,

Thank you for your patient and your advice. It worked when I set up relationship with two tables. 

All the best,

Paris