How to create a measures to get quarter values?

[Mavericks334]

Hi,

How do i create measures to calculate current month quarter values, previous month quarter values and last year same quarter values.

I have a date table and Raw data table and month table to get the quarters? Any suggestions how this could be achieved.

I am using this measure to get YTD values. DATESBETWEEN(Dates[Dates],DATEADD(LASTDATE(Year_Period[Next_Month_Start_Date]),MAX(Year_Period[Fiscal_Period])*-1,MONTH),LASTDATE(Year_Period[Month_End_Date]))

What do i modify to get the quarter values?

Regards,
Renato.

 

[akice]

if already have a calendar/Date table (that must have no gaps in dates. Needs to be all inclusive for all dates you are gong to report on and have complete years. Plus should "Mark as Date Table" as best practice). then the time intelligence functions are much easier to use and easier to do code review on.

Current Year YTD:= TOTALYTD( SUM ( Table[Values] ), Dates[Dates])

Current QTD := TOTALQTD( SUM(Table[Values]), Dates[Dates])

Previous QTD := TOTALQTD( SUM(Table[Values]), PREVIOUSQUARTER( Dates[Dates] ) )

Previous QTD 2 := TOTALQTD( SUM(Table[Values]), PARALLELPERIOD ( Dates[Dates], -1, quarter ) )

Last Year same Quarter := TOTALQTD( SUM(Table[Values]), SAMEPERIODLASTYEAR( Dates[Dates] ) )

These function may or may not be exactly what you want. There are many more Time Intelligence functions that return slightly different results so would be good for you to read up on them to make sure it is calculating exactly as needed. You can Google the functions for reference info.

Matthew

 

[Mavericks334]

Thanks Mattew. I tried the same it did not work. I have a slicer for Month on top. Could that be a problem

 

[akice]

Re-reading your post I see you have a separate table for month so yes it will cause a problem. How are linking between Dates[Date] and your fact table? By the date column right? Time intelligence functions only manipulate the 'Date' table and don't have any effect on filters on other tables. if you want a slicer for Month, it should be in the 'Dates' table not in its own. Add two calculated columns (NOT Calculated Fields or Measures as they're known in other editions) to 'Dates' table:

PeriodName = FORMAT(Dates[Dates], "yyy")

PeriodNumber = MONTH(Dates[Dates]

Choose option to 'Sort by Column' then set sort: PeriodName by: PeriodNumber. Then in pivot add PeriodName as a slicer. Remove other month slicer (clearing its filters first). Then it should all work.

Matthew

 

[Mavericks334]

Thanks Matthew. The problem for me is year starts from the Month of July and not Jan, so using TOTAL YTD with the above solution does not work for me. I need to custom build that. If i try and use the above methods i do not get any value for my YTD.

Regards,
Renato.

 

[akice]

The syntax for TOTALYTD is: "TOTALYTD(expression,dates[,filter][,year_end_date])" with the last parameter specifying what the year end date is. Just do like I say above, but put:

Current Year YTD:= TOTALYTD( SUM ( Table[Values] ), Dates[Dates],"6/30")

and a correction on the above post, for PeriodName it should be:

PeriodName = FORMAT(Dates[Dates], "mmm")