Forum Discussion

Ericson_BI's avatar
Ericson_BI
Regular Visitor
3 years ago

Power BI Measure doesnt sum up counted values

2

I have the following formula, which should count the rows without a document number, based on the selected month. This formula does this, but it does not add up the numbers. Example: If I have 10 rows without a document number for the month of January and 5 rows without a document number for the month of February, then the bar chart should be at 10 in January and 15 in February (10+5) and so on. The interesting thing is that the same formula works for a table from another year. How can I fix this problem that this formula does not show the counted rows month to month as individual values, but always adds them up.

AA-Projects2022 = IF(
    ISFILTERED('AA-Projects'[date of capture]),
    ERROR("Quickmeasures with time intelligence can only be grouped or filtered using the date hierarchy provided by Power BI or the primary date column."),
    TOTALYTD(
        COUNTA('AA-Projects'[document number]),
        'AA-Projects2022'[Entry Day].[Date], 'AA-Projects'[Year]=2022
    )
)
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Ericson_BI , for Time intelligence always use date table 

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

     

    or

     

    YTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1*MONTH(_max))+1
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     

    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

     

    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

     

    Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
    , "Year", year([date])
    , "Month Year", format([date],"mmm-yyyy")
    , "Month year sort", year([date])*100 + month([date])
    , "Qtr Year", format([date],"yyyy-\QQ")
    , "Qtr", quarter([date])
    , "Month",FORMAT([Date],"mmmm")
    , "Month sort", month([DAte])
    , "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
    , "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
    ,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
    , "Month Type", Switch( True(),
    eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
    eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
    Format([Date],"MMM-YYYY") )
    ,"Year Type" , Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )
    )

  • Ericson_BI's avatar
    Ericson_BI
    Regular Visitor
    3 years ago

    amitchandak Hi, thank you for the answer. Can you please relate this to my fields? I am still a beginner in this area. And as already described, the only problem is that it doesn't add up the numbers. Example: If I filter by January and February on the dashboard, I get the respective numbers. But the numbers should be added up. February should result from January + February. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Ericson_BI ,

     

    Please try this measure.

    AA-Projects2022 =
IF (
    ISFILTERED ( 'AA-Projects'[date of capture] ),
    ERROR ( "Quickmeasures with time intelligence can only be grouped or filtered using the date hierarchy provided by Power BI or the primary date column." ),
    CALCULATE (
        COUNTA ( 'AA-Projects'[document number] ),
        'AA-Projects2022'[Entry Day] <= MAX ( 'AA-Projects2022'[Entry Day] )
            && 'AA-Projects'[Year] = 2022,
        ALL ( 'AA-Projects2022' )
    )
)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Ericson_BI's avatar
      Ericson_BI
      Regular Visitor
      3 years ago

      Anonymous I get the error message that no column named "Year" exists, although I have such a column in my table. How can I proceed?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Ericson_BI ,

         

        Not sure, please check the code above for spelling errors.  You can also attach screenshots to illustrate the details, please take care to obscure private information.

        Best Regards,
        Gao
        Community Support Team