Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI DAX formula

Hi,

 

I've a table below:

 

account_codenameeffective_frommonth_effective_fromYear_effective_fromMonth-Year-effective_fromeffective_tomonth_effective_toYear_effective_toaccount_type_code
OG001Oil & Gas2017-07-14 07:41:06.7247227 +00:00717Jul-172018-10-26 10:43:16.6078098 +10:30October2018mining
BNK001Banking2017-07-14 07:41:06.7247227 +00:00717Jul-172018-10-26 10:43:16.6078098 +10:30October2018Finance
EDU001University2017-07-14 07:41:06.7247227 +00:00717Jul-172018-10-26 10:43:16.6078098 +10:30October2018Education
TEC001Technoogy2017-07-14 07:41:06.7247227 +00:00717Jul-172018-10-26 10:43:16.6078098 +10:30October2018IT
OG001Oil & Gas (Asia)2018-10-26 10:43:16.6546562 +10:301018Oct-182019-06-06 15:53:16.1318654 +09:30June2019mining
BNK001Banking (Asia Pacific)2018-10-26 10:43:16.6546562 +10:301018Oct-182019-05-19 16:33:17.1352558 +09:30May2019Finance
EDU001University (Oceania)2018-10-26 10:43:16.6546562 +10:301018Oct-182019-05-19 16:33:17.1352558 +09:30May2019Education
TEC001Technoogy (Worldwide)2018-10-26 10:43:16.6546562 +10:301018Oct-182019-05-19 16:33:17.1352558 +09:30May2019IT
OG001Oil & Gas (Asia)2019-06-06 15:53:16.1318654 +09:30619Jun-19   mining
BNK001Banking (Asia Pacific)2019-05-19 16:33:17.1352558 +09:30519Jun-19   Finance
EDU001University (Oceania)2019-05-19 16:33:17.1352558 +09:30519Jun-192019-07-20 02:35:21.7312951 +09:30July2019Education
TEC001Technoogy (Worldwide)2019-05-19 16:33:17.1352558 +09:30519Jun-19   IT
MED001Hospital2020-07-20 02:35:21.7312951 +09:30720Jul-20    

 

I'm using a drop down filter in Power Bi for users to select Month-Year-effective_from. Once the user selects that, I have to list the count of all active account codes which was present in that month.

 

Eg: When user selects Jul-20, it should display a total count of 4.

 

Can someone please help me with formula for the above?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    effective_from_format =
var _year=LEFT('Table'[effective_from],4)
var _month=MID('Table'[effective_from],6,2)
var _day = MID('Table'[effective_from],9,2)
return
DATE(_year,_month,_day)
    Month Year = FORMAT([Month-Year-effective_from],"mmm-yy")
    To = IF([effective_to]=BLANK(),DATE(9999,12,31),[effective_to])

    2. Create calculated table.

    Table 2 = SUMMARIZE('Table',[Month Year])

    3. Create measure.

    Measure =
var _date=SELECTEDVALUE('Table 2'[Month Year])
var _1=CALCULATE(MAX('Table'[effective_from_format]),FILTER(ALL('Table'),[Month Year]=_date))
return
COUNTX(FILTER(ALL('Table'),[effective_from_format]<=_1&&[To]>=_1),[account_code])

    4. Place Table 2 [Month Year] in the slicer, and place [Measure] in the card chart.

    5. Result:

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    try this measure:

    Measure=
VAR _Date =
    SELECTEDVALUE ( 'Table'[Month-Year-effective_from], TODAY () )
VAR _LdOfM =
    EOMONTH ( _Date, 0 )
RETURN
    CALCULATE (
        COUNTA ( 'Table'[account_code] ),
        FILTER (
            FILTER ( ALL ( 'Table' ), 'Table'[Month-Year-effective_from] <= _Date ),
            ISBLANK ( 'Table'[effective_to] )
                || 'Table'[effective_to] > _LdOfM
        )
    )

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    effective_from_format =
var _year=LEFT('Table'[effective_from],4)
var _month=MID('Table'[effective_from],6,2)
var _day = MID('Table'[effective_from],9,2)
return
DATE(_year,_month,_day)
    Month Year = FORMAT([Month-Year-effective_from],"mmm-yy")
    To = IF([effective_to]=BLANK(),DATE(9999,12,31),[effective_to])

    2. Create calculated table.

    Table 2 = SUMMARIZE('Table',[Month Year])

    3. Create measure.

    Measure =
var _date=SELECTEDVALUE('Table 2'[Month Year])
var _1=CALCULATE(MAX('Table'[effective_from_format]),FILTER(ALL('Table'),[Month Year]=_date))
return
COUNTX(FILTER(ALL('Table'),[effective_from_format]<=_1&&[To]>=_1),[account_code])

    4. Place Table 2 [Month Year] in the slicer, and place [Measure] in the card chart.

    5. Result:

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly