Forum Discussion

lsihui_'s avatar
lsihui_
Frequent Visitor
2 years ago

Difficulties when trying to calculate differences between previous months for multiple brands

Hi, 

 

I'm trying to create a matrix on PowerBI to show the differences of current vs previous month. I have managed to do it for one brand (screenshot1). But I'm getting this error when I tried to add another brand into the dataset (screenshot2).

 

The current relationship between the date table and TV table is "many-to-one" (screenshot3). I've tried to change the relationship to "many-to-many" but ended up getting another error (screenshot4)

 

Appreciate your help to guide me on this as I am pretty new to Power BI.

 

Screenshot1: Achieved desired output using 1 brand. 

 

Screenshot2: Error after I added another brand into the dataset. The dataset does contain duplicated months, but it is for different brands. 

 

Screenshot3: Current relationship for the original database (with only 1 brand)

 

Screenshot4: Error after trying to change to "many-to-many"

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    lsihui_ , Make sure you have created a date table using the Calendar function or got it from the source. The relation should be 1-Many, where Date table is on one side. 

     

    Make sure date table has no duplicate dates.

     

    After that measures like these should work

     


    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]))
    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]))
    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
    this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
    last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))
    last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*month(Today()),MONTH)))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    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.

    • lsihui_'s avatar
      lsihui_
      Frequent Visitor
      2 years ago

      Thanks for getting back. Yes, the date table was created using the calendar function. 

      However I'm unable to change the relationship to "one-to-many" as circular dependency was detected.