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
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Have never seen COALESCE 😄

 

Did you try taking it out on only use SUM?

sum(Actuals_by_Month[AMOUNT])

 

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 yeah, tried just the sum without COALESCE. No luck ðŸ˜“

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

     Anonymous 

    hmm

    Is the field you put into the slicer from the calendar table or from the fact table?

     

    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  from Calendar.

      • Mikelytics's avatar
        Mikelytics
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        Anonymous 

        Ok, so slowly I reach the limit of ideas ^^

        You mentioned you have other dimensions connected to the fact table. 

        so question 1) is there maybe another filter which is active from another dimension which causes the issue?

        questions 2) is there ,maybe by accident, antother relation from another table to the calendar table, maybe auto-connected by Power BI? Can you please show the overview of relations as wel as if any of this table have an active filter? if there is any try to turn them of in the report.

         

        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.