Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

unable to get dax function previousmonth, previousyear,parallel functions working (tried everything)

Hi, I am unable to get any of the previous functions (previousday, previousmonth, previousquarter, previousYear and parallelperiod)
Working. Every single version of this I've tried with no success and every different variation has failed.
Heres my relationship. Databricks is the name of the table, linked by date to a calendar table,each record in the calendar table is a unique record by Date.

Here's a sample of the data in databricks

Every version I tried of Previous functions, parallelperiod and everything else fails.  On one try I get nothing in the previous column, in another try I get incorrect values.  

I cannot seem to figure out what the problem is.I've spent at least a full day attempting to find a soution with a failure each time.
Even the simplist attempt of a measure has failed

CALCULATE(SUM(Databricks[ReturnVisit]), PREVIOUSMONTH('Calendar'[Date])),
This version puts blanks in the measure rather than values.

Any help would be appreciated.  Thank you .
Steve


Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Can you change the data type of date as datetime and check there is not timestamp. If time is other than 12:00 AM then create date only column and join with date table

     

    Date only = datevalue([date])

     

    You need to create a measure

    CALCULATE(SUM(Databricks[ReturnVisit]), PREVIOUSMONTH('Calendar'[Date]))

     

    But if no date is selected it considers first date and will not give a value.

     

    Example options

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    If no ate is selected then use today

     

    This Month =
    var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     

    Last Month =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max = eomonth(_max1,-1)
    var _min = eomonth(_max1,-2)+1
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your help.
      I haven't checked the youtube videos yet, but I have to get previous month for any date that 's in the data so I would have to be able to show previous month as January when the table.
      As you can see, i have dates for months going back..and i would hope that in the _previous column, it would show previous months data.