Forum Discussion

domiowa's avatar
domiowa
Frequent Visitor
8 years ago

Sum by month

Hi everyone, I have a problem to do something very simple on excel but so hard on power BI I have a project table with my project Name, starting date, excepted ending date and ending date So I'd l...
Need Help
jdobrzen's avatar
jdobrzen
Advocate III
8 years ago

If you're trying to figure out the difference between Starting Date and Ending Date where the difference is 1 month, could you solve that with a calculated column and then a mesure to count the instances?

 

+1M Indicator Column = IF(DATEDIFF(Starting Date, EndingDate, month) = 1, TRUE(), FALSE())

 

Count +1M Finish = COUNTROWS(FILTER(myTable, +1M Indicator Column = TRUE()))

domiowa's avatar
domiowa
Frequent Visitor
8 years ago

Thanks for your answer but the problem is I'll have other calculation to do so the difference won't be all the time one month

  • jdobrzen's avatar
    jdobrzen
    Advocate III
    8 years ago

    I'll take one more shot :)

     

    If you need it to be dynamic so it has to be a measure, then maybe you can leverage a parameter table.  It looks like you might already be trying to do that with your seperate Month table.

     

    https://www.daxpatterns.com/parameter-table/

     

    = IF (HASONEVALUE ( myParmTable[myParm] ),

                    COUNTROWS(FILTER(myTable, DATEDIFF(Starting Date, EndingDate, month) = VALUES(myParmTable[myParm])))

                    ,0

    )

    • domiowa's avatar
      domiowa
      Frequent Visitor
      8 years ago
      I tried it but it doesn't work
      Weird how things which are so easy on excel are so hard on BI