Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 months showing same value

Hi, I am trying to calculate last 12 months of data based on slicer "last day of month" . the code I used is below  Last 12 months v4 = VAR MaxDate = MAX( 'Calendar'[Calendar_last_day_of_month] )...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Mikelytics , I was able to make this work but by different method - "USERELATIONSHIP". Here is the code: 

    _previous_amount =
    var ReferenceDate = MAX('Calendar'[Calendar_last_day_of_month])
    VAR PreviousDates =
    DATESINPERIOD('Previous Date'[Calendar_last_day_of_month],ReferenceDate, -13, MONTH)
    var result =
    CALCULATE(sum(ACTUALS_BY_MONTH[AMOUNT]),
    REMOVEFILTERS('Calendar'),
    KEEPFILTERS(PreviousDates) ,USERELATIONSHIP('Calendar'[Date], 'Previous Date'[Date]))
    RETURN
    result

    *************

    Previous Date ='Calendar'

     

    Results:

    Here is Youtube reference that I followed 

    https://www.youtube.com/watch?v=d8Rm7dwM6gc 

    Thanks for all of your suggestions and time!

Mikelytics's avatar
Mikelytics
Resident Rockstar
3 years ago

Anonymous 

Weird. I think I now set it up exactly like you. I added a LastDayOfMonthColum in my data table and adjsuted the formula by refering to the new column and it works

 

Date Table

 

Formula

 

Last 12 months v4 = 
VAR MaxDate = MAX( Dim_Date[LastDayOfMonth] )     -- retrieve latest date
VAR MinDate = EDATE( MaxDate, -12 )   -- move it back 12 months
VAR Result =
     CALCULATE(
          [Aggregation | Sum],
          FILTER( ALL( Dim_Date ),      -- return period between
          Dim_Date[LastDayOfMonth]  <= MaxDate &&  -- latest date
          Dim_Date[LastDayOfMonth] > MinDate )    -- bigger than year before
     )
RETURN
     Result

 

Result

 

What is about your data model. Which Columns are connected between dimension and fact? What is the relation type and what the filter direction? Can you show a screenshpt please?

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    my data model is pretty simple but the fact table is joined with few dimensions wiht one- to- many. however the re;lationship between calendar and fact table is defined by a relationship as screenshot

     

    • Mikelytics's avatar
      Mikelytics
      Resident Rockstar
      3 years ago

      Hi Anonymous 

       

      it looks totally the same on my side. But there is one thing I observed. Please look on the screenshot what I highlighted

      is this a measure? or a column reference?

      normally it should not give something back but is it possible that you need to ^replace it by 

       SUM('Actuals_by_Month'[Actuals_Volume]) ??

       

      Can you pleae show the measure definition you have?

       

      Best regards
      Michael
      -----------------------------------------------------
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
      Appreciate your thumbs up!
      @ me in replies or I'll lose your thread.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Mikelytics , Actuals_Volume is a measure defined as below

        Actuals_Volume = COALESCE(sum(Actuals_by_Month[AMOUNT]),0)