Power BI Measure doesnt sum up counted values

[Ericson_BI]

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

[v-cgao-msft]

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]

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

[v-cgao-msft]

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

[Ericson_BI]

@v-cgao-msft I have already checked everything, everything is correct, but unfortunately it does not currently find the "Year" column. 

[Ericson_BI]

@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. 

[amitchandak]

@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-bd52912a5b...

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