Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Count based on Custom Slicer Selection.

I have this measure in power bi --> countinvoices = COUNTROWS(invoicetable). 

I am using a calendarauto(6) date table with a column in called FY YYYY Q# = IF(MONTH('FY Calender'[Date])>6,"FY "&YEAR('FY Calender'[Date])+1&" "&FORMAT(EDATE('FY Calender'[Date], -6),"\QQ"),"FY "&YEAR('FY Calender'[Date])&" "&FORMAT(EDATE('FY Calender'[Date], -6),"\QQ")).

 

My question is how to count the YTD total of the countinvoices measure based on the slicer selection. For example, If I click the slicer FY 2025 Q2 it would count YTD from December back to July.  If I click the slicer FY 2025 Q1 it would count YTD from September back to July.

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    If your date table is marked as a date table you can use the 2nd parameter to DATESYTD to specify when the year ends, so you can create a measure like

    Num invoices FYTD =
CALCULATE (
    COUNTROWS ( invoicetable ),
    DATESYTD ( 'FY Calendar'[Date], "6-30" )
)

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

     

    First mark your calendar table as a dates table.

    Create this measure:

    FYTD =
CALCULATE ( [MEASURE], DATESYTD ( CalendarTable[Date], "Jun 30" ) )
--FY ends on June 30

     

     

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    If your date table is marked as a date table you can use the 2nd parameter to DATESYTD to specify when the year ends, so you can create a measure like

    Num invoices FYTD =
CALCULATE (
    COUNTROWS ( invoicetable ),
    DATESYTD ( 'FY Calendar'[Date], "6-30" )
)