Forum Discussion

MrDaniels's avatar
MrDaniels
Frequent Visitor
3 years ago

Calculation between different columns (columns / measures) Pivot Table

Hi gurus,

 

I have a simple problem that I cannot find the solution. I think I have a missunderstanding with Columns and measures, you might be able to help me.

 

I have a simple table with items and two dates (target and finish) for each item.  

 

1. Need to calculate the difference in months (between target and finish) for each item: I did this by creating a column using datediff.

2.Based on the difference I need to allocate a category (e.g. Earlier, on Target, 1 month later, +2 month later): I did this by creating another column using an IF statement.

 

 

Where I need help is that I need to create something similar to a pivot where is going to count these categories, and then get me a percentage.

 

I used Matrix table, putting Category in the columns and counting them in the values to get this pivot.

Now I need to divide (Earlier + Target Month) / Total = (2+14)/24 = 67%

 

Im not sure if I can solve this using power query or DAX.

 

Could you please share the light.

 

Thanks in advance.

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    MrDaniels , Based on what I got, You need to use time intelligence with date table 

     

    example

    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])))

     

    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