PREVIOUSMONTH

[sshweky]

Hi - I am having trouble with comparing date peiods and I really need direction. Please see my statement below.

       ShpdLast30 = CALCULATE (sum(InvoiceDetail[QtyShpd]), PREVIOUSMONTH(Dates[Date]))

I don't understand the role of the Dates table & how the PREVIOUSMONTH function knows what the current month is to compare it to?

Thank you!!

Steven

[Greg_Deckler · Accepted solution]

You have to read the fine print here to understand how PREVIOUSMONTH (and similar functions) work:

https://support.office.com/en-US/article/PREVIOUSMONTH-Function-DAX-72fb8dda-6cd6-49e6-b9b4-8a5f6324...

"This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009."

So, mainly you have to use this in a "context aware way". Here is the example from the page:

Example

The following sample formula creates a calculated field that calculates the 'previous month sales' for the Internet sales.

To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a calculated field, named Previous Month Sales, using the formula defined in the code section, to the Values area of the PivotTable.

=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))

My understanding of how this works is that the row labels are context filters that filter the 'DateTime' table to that specific year and month, so in the context of the pivot table (matrix) for each particular row, the 'DateTime' table has been filtered such that when you pass PREVIOUSMONTH the 'DateTime'[DateKey] column, you are only passing in the dates for a specific year and month, like June 2009. Thus, PREVIOUSMONTH sees that and goes and grabs the dates for the previous month and passes those back as filters to the CALCULATE function. I don't think that the page really explains it all very well, but that is my understanding of how it is supposed to work.

Here is a good article on context in DAX formulas that might help as well.

https://support.office.com/en-US/article/Context-in-DAX-Formulas-2728fae0-8309-45b6-9d32-1d600440a7a...

[DominikPetri]

Hi smoupre,

yes, PREVIOUSMONTH always returns all dates from the previous month (in the current filter context). If you add the "Days" field to the rows of your PivotTable, your formula returns the SalesAmount for ALL days of the previous month and not only for the SAME day of the previous month. As long as you stay with MonthNumberOfYear in rows, you are fine...

If you use

= CALCULATE (
    SUM ( InternetSales_USD[SalesAmount_USD] ),
    DATEADD ( 'DateTime'[DateKey], -1, MONTH )
)

you'll get the correct SalesAmount on a daily and monthly basis

To prevent the incorrect result from showing in the subtotal row for CalendarYear and in the Grand Total, you can use

= IF (
    HASONEVALUE ( 'DateTime'[MonthNumberOfYear] ),
    CALCULATE (
        SUM ( InternetSales_USD[SalesAmount_USD] ),
        DATEADD ( 'DateTime'[DateKey], -1, MONTH )
    ),
    BLANK ()
)

Best regards

Dominik Petri

[sshweky]

Got it! Let's say I want to do same period last year, How do I make the visual always open to YTD filter? Now I understand the formula, but I can't set the filter to make the formula work without actually using the filters along the side. So for example, every day when I open my dashboard, I want to see YTD sales & sales of same period LY. Make sense?

[greggyb]

PBI filters do not allow programmatic criteria. You can't set the filter on a date to < TODAY().

You have to set a literal value in a filter.

This makes it seems like an awful problem to solve, but it's actually trivial to do at a level before the presentation layer.

Create a field in your date dimension for CurrentYTD. Then you can filter on [CurrentYTD] = True.

Power Query offers a very useful function for adding this field to any date dimension Date.IsInYearToDate(). As you refresh your model daily, this field will always be up to date, and you never have to change your filter criteria.

[Willborn]

Hi Greggyb

You wrote:

Create a field in your date dimension for CurrentYTD. Then you can filter on [CurrentYTD] = True.

How can I do this?

I'm currently struggeling with time issues, this might help me. I already added and linked a date table:

Thanks and best regards

Patrick

[sshweky]

OK... that was awesome ... thanks! Next issue ... My view is showing all current YTD values and then I want to show SamePeriodLastYear values alongside as a comparison. But once I use the YTD page level filter, then all previous dates are no longer available to use the same period LY function??

[greggyb]

You'll need two measures. The first is a simple sum:

SimpleSum =
SUM( 'Table'[Field] )

Then you need to create your second measure using SAMEPERIODLASTYEAR():

LastYearSimpleSum =
CALCULATE(
    [SimpleSum]
    ,SAMEPERIODLASTYEAR( DimDate[Date] )
)

Now when you create your visualizations, put both of these measures into each.

You can use a page-level filter on CurrentYTD = True. Both [SimpleSum] and [LastYearSimpleSum] are evaluated in the filter context. [SimpleSum] gives you the current year YTD values. [LastYearSimpleSum] examines the dates in context (current year YTD), and shifts them back one year. Thus you have one set of display dates, but your measures are being evaluated in two separate years.

[DominikPetri]

Hi Steven,

here are some useful links to get you started:

Time Intelligence in DAX

The Ultimate Date Table revisited

Understand and create date tables in Power Pivot in Excel

Hope this helps!

Best regards

Dominik

[GilesWalker]

Hi Steven,

With dates I have created a date table manually in Excel which I link to as a CSV file. However with the December update you could copy and paste the manual date key excel data directly into PBI through use of the enter data button on the ribbon.

My date key table looks like this:

I create a relationship between my main data and the date key column. This then allows me to use slicers or filters for dates, then when I use formula such as SAMEPERIODLASTYEAR, PREVIOUSMONTH, YTD etc... I know the system will look up the historic data based of off whatever my slicers say. An example of how I use this would be with looking at the last three months of data compared to last years same three months. In my slicer I select this year and relevant months. The visual then knows to compare SAMEPERIODLASTYEAR of the chosen months in the slicer. 

This way I know the system is looking at solid data that I am telling it to.

There may be better ways but this works for me.

Giles