Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Measures for distinct New clients - Various Finanical Year Time intelligence

‎09-06-2021 08:44 PM

Hi Community,

 

I need help in creating measures for distinct new clients Last FY, FYTD, Last Quarter, Current Quareter, Last month, This month

Please note:

Financial Year starts on July 1.

So, last FY = 1 Jul 20 - 30 Jun 2021

FYTD = 1 Jul 21 - today

 

I want the measures to work without any date filters selected.

Follwoing is my sample data set & expected result for some of the measures. Measure for 'This month' is in the Pbi file.

ExcelPBI_0-1630985523814.png

 

 Pbi file : https://1drv.ms/u/s!Ag919_pO_UKrgRsvDQaPDqbiFQhz?e=Lnly24

 

Many Thanks!

Labels:
Message 1 of 7
479 Views
0
2 ACCEPTED SOLUTIONS
v-angzheng-msft
Community Support
Community Support

‎09-09-2021 12:05 AM

Hi, @Anonymous 

 

According to the picture above, Program C has 2 new clients for this month.

vangzhengmsft_0-1631170280696.pngBased on the corrected results, here are my calculated measures.

vangzhengmsft_3-1631170635440.png

 

Let's preview the final result:

vangzhengmsft_2-1631170509205.png

First create 2 measures to calculate the fiscal year and this year:

_FYYear = 
var _currentDate=MAX('Table'[Service_date])
VAR _fy =
    IF (
        MONTH (_currentDate) <= 6,
        VALUE ( FORMAT ( _currentDate, "YY" ) ) - 1,
        VALUE ( FORMAT ( _currentDate, "YY" ) )
    )
RETURN
   _fy
_ThisYear = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _thisyear=CALCULATE([_FYYear],FILTER(ALL('Table'),'Table'[Service_date]=_maxDate))
return _thisyear

Then create 4 measures to identify new customers, and finally count the new customers identified.

_isNew_LFY = 
var _Before_LFY=[_ThisYear]-2
var _lastFY=[_ThisYear]-1
var _thisFY=[_ThisYear]

var _B_LFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_Before_LFY),[Client_id])
var _lastFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_lastFY),[Client_id])

var _B_LFY_New=IF('Table'[_FYYear]=_lastFY,IF(NOT(MAX('Table'[Client_id]) in _B_LFY_table),1,0))
return _B_LFY_New
_isNew_FYTD = 
var _Before_LFY=[_ThisYear]-2
var _lastFY=[_ThisYear]-1
var _thisFY=[_ThisYear]

var _B_LFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]=_Before_LFY),[Client_id])
var _lastFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_lastFY),[Client_id])

var _FYTD_count=IF('Table'[_FYYear]=[_ThisYear],IF(NOT(MAX('Table'[Client_id]) in _lastFY_table),1,0))
return _FYTD_count
_isNew_lastMonth = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _Before_LM=EOMONTH(_maxDate,-2)
var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LM_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_Before_LM),[Client_id])
var _lastMonth_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_lastMonth),[Client_id])

var _New_lastMonth=IF(_currentDate>_Before_LM&&_currentDate<=_lastMonth,IF(NOT(MAX('Table'[Client_id]) in _B_LM_table),1,0))
return _New_lastMonth
_isNew_thisMonth = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _Before_LM=EOMONTH(_maxDate,-2)
var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LM_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_Before_LM),[Client_id])
var _lastMonth_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_lastMonth),[Client_id])

var _New_thisMonth=IF(_currentDate>_lastMonth&&_currentDate<=EOMONTH(_currentDate,0),IF(NOT(MAX('Table'[Client_id]) in _lastMonth_table),1,0))
return _New_thisMonth

Count client:

_countNew_B_LFY = 
var _t=FILTER('Table',[_isNew_LFY]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id
_countNew_FYTD = 
var _t=FILTER('Table',[_isNew_FYTD]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id
_countNew_LastMonth = 
var _t=FILTER('Table',[_isNew_lastMonth]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)
_countNew_thisMonth = 
var _t=FILTER('Table',[_isNew_thisMonth]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id

So the result:

vangzhengmsft_5-1631171102056.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

How to get your questions answered quickly -- How to provide sample data

View solution in original post

W25_Measures for distinct New clients - Various Finanical Year Time intelligence.pbix
Message 4 of 7
324 Views
v-angzheng-msft
Community Support
Community Support

‎09-10-2021 12:52 AM

Hi, @Anonymous 

Here are the measures you want:

_isNew_lastQuarter = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _thisQuarter=QUARTER(_maxDate)
var _startOfThisQuarter=(_thisQuarter-1)*3+1
var _thisyear=YEAR(_maxDate)

var _s_Qdate=DATE(_thisyear,_startOfThisQuarter,1)

var _s_B_L_Q=EOMONTH(_s_Qdate,-7)
var _e_B_L_Q=EOMONTH(_s_Qdate,-4)

var _e_L_Q=EOMONTH(_s_Qdate,-1)

var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LQ_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_s_B_L_Q&&'Table'[Service_date]<=_e_B_L_Q),[Client_id])
var _lastQuarter_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_e_B_L_Q&&'Table'[Service_date]<=_e_L_Q),[Client_id])

var _New_lastQuarter=IF(_currentDate>_e_B_L_Q&&_currentDate<=_e_L_Q,IF(NOT(MAX('Table'[Client_id]) in _B_LQ_table),1,0))
return _New_lastQuarter
_isNew_thisQuarter = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _thisQuarter=QUARTER(_maxDate)
var _startOfThisQuarter=(_thisQuarter-1)*3+1
var _thisyear=YEAR(_maxDate)

var _s_Qdate=DATE(_thisyear,_startOfThisQuarter,1)

var _s_B_L_Q=EOMONTH(_s_Qdate,-7)
var _e_B_L_Q=EOMONTH(_s_Qdate,-4)

var _e_L_Q=EOMONTH(_s_Qdate,-1)

var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LQ_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_s_B_L_Q&&'Table'[Service_date]<=_e_B_L_Q),[Client_id])
var _lastQuarter_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_e_B_L_Q&&'Table'[Service_date]<=_e_L_Q),[Client_id])

var _New_thisQuarter=IF(_currentDate>_s_Qdate&&_currentDate<=_maxDate,IF(NOT(MAX('Table'[Client_id]) in _lastQuarter_table),1,0))
return _New_thisQuarter
_countNew_LastQuarter = 
var _t=FILTER('Table',[_isNew_lastQuarter]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)
_countNew_ThisQuarter = 
var _t=FILTER('Table',[_isNew_thisQuarter]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)

 

Result:

vangzhengmsft_0-1631260304621.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data

View solution in original post

W25_Measures for distinct New clients - Various Finanical Year Time intelligence.pbix
Message 7 of 7
248 Views
6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

‎09-10-2021 12:52 AM

Hi, @Anonymous 

Here are the measures you want:

_isNew_lastQuarter = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _thisQuarter=QUARTER(_maxDate)
var _startOfThisQuarter=(_thisQuarter-1)*3+1
var _thisyear=YEAR(_maxDate)

var _s_Qdate=DATE(_thisyear,_startOfThisQuarter,1)

var _s_B_L_Q=EOMONTH(_s_Qdate,-7)
var _e_B_L_Q=EOMONTH(_s_Qdate,-4)

var _e_L_Q=EOMONTH(_s_Qdate,-1)

var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LQ_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_s_B_L_Q&&'Table'[Service_date]<=_e_B_L_Q),[Client_id])
var _lastQuarter_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_e_B_L_Q&&'Table'[Service_date]<=_e_L_Q),[Client_id])

var _New_lastQuarter=IF(_currentDate>_e_B_L_Q&&_currentDate<=_e_L_Q,IF(NOT(MAX('Table'[Client_id]) in _B_LQ_table),1,0))
return _New_lastQuarter
_isNew_thisQuarter = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _thisQuarter=QUARTER(_maxDate)
var _startOfThisQuarter=(_thisQuarter-1)*3+1
var _thisyear=YEAR(_maxDate)

var _s_Qdate=DATE(_thisyear,_startOfThisQuarter,1)

var _s_B_L_Q=EOMONTH(_s_Qdate,-7)
var _e_B_L_Q=EOMONTH(_s_Qdate,-4)

var _e_L_Q=EOMONTH(_s_Qdate,-1)

var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LQ_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_s_B_L_Q&&'Table'[Service_date]<=_e_B_L_Q),[Client_id])
var _lastQuarter_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]>_e_B_L_Q&&'Table'[Service_date]<=_e_L_Q),[Client_id])

var _New_thisQuarter=IF(_currentDate>_s_Qdate&&_currentDate<=_maxDate,IF(NOT(MAX('Table'[Client_id]) in _lastQuarter_table),1,0))
return _New_thisQuarter
_countNew_LastQuarter = 
var _t=FILTER('Table',[_isNew_lastQuarter]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)
_countNew_ThisQuarter = 
var _t=FILTER('Table',[_isNew_thisQuarter]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)

 

Result:

vangzhengmsft_0-1631260304621.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data
W25_Measures for distinct New clients - Various Finanical Year Time intelligence.pbix
Message 7 of 7
249 Views
v-angzheng-msft
Community Support
Community Support

‎09-09-2021 12:05 AM

Hi, @Anonymous 

 

According to the picture above, Program C has 2 new clients for this month.

vangzhengmsft_0-1631170280696.pngBased on the corrected results, here are my calculated measures.

vangzhengmsft_3-1631170635440.png

 

Let's preview the final result:

vangzhengmsft_2-1631170509205.png

First create 2 measures to calculate the fiscal year and this year:

_FYYear = 
var _currentDate=MAX('Table'[Service_date])
VAR _fy =
    IF (
        MONTH (_currentDate) <= 6,
        VALUE ( FORMAT ( _currentDate, "YY" ) ) - 1,
        VALUE ( FORMAT ( _currentDate, "YY" ) )
    )
RETURN
   _fy
_ThisYear = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _thisyear=CALCULATE([_FYYear],FILTER(ALL('Table'),'Table'[Service_date]=_maxDate))
return _thisyear

Then create 4 measures to identify new customers, and finally count the new customers identified.

_isNew_LFY = 
var _Before_LFY=[_ThisYear]-2
var _lastFY=[_ThisYear]-1
var _thisFY=[_ThisYear]

var _B_LFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_Before_LFY),[Client_id])
var _lastFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_lastFY),[Client_id])

var _B_LFY_New=IF('Table'[_FYYear]=_lastFY,IF(NOT(MAX('Table'[Client_id]) in _B_LFY_table),1,0))
return _B_LFY_New
_isNew_FYTD = 
var _Before_LFY=[_ThisYear]-2
var _lastFY=[_ThisYear]-1
var _thisFY=[_ThisYear]

var _B_LFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]=_Before_LFY),[Client_id])
var _lastFY_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[_FYYear]<=_lastFY),[Client_id])

var _FYTD_count=IF('Table'[_FYYear]=[_ThisYear],IF(NOT(MAX('Table'[Client_id]) in _lastFY_table),1,0))
return _FYTD_count
_isNew_lastMonth = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _Before_LM=EOMONTH(_maxDate,-2)
var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LM_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_Before_LM),[Client_id])
var _lastMonth_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_lastMonth),[Client_id])

var _New_lastMonth=IF(_currentDate>_Before_LM&&_currentDate<=_lastMonth,IF(NOT(MAX('Table'[Client_id]) in _B_LM_table),1,0))
return _New_lastMonth
_isNew_thisMonth = 
var _maxDate=CALCULATE(MAX('Table'[Service_date]),ALL('Table'))
var _currentDate=MAX('Table'[Service_date])

var _Before_LM=EOMONTH(_maxDate,-2)
var _lastMonth=EOMONTH(_maxDate,-1)

var _B_LM_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_Before_LM),[Client_id])
var _lastMonth_table=SUMMARIZE(FILTER(ALL('Table'),'Table'[Program]=MAX('Table'[Program])&&'Table'[Service_date]<=_lastMonth),[Client_id])

var _New_thisMonth=IF(_currentDate>_lastMonth&&_currentDate<=EOMONTH(_currentDate,0),IF(NOT(MAX('Table'[Client_id]) in _lastMonth_table),1,0))
return _New_thisMonth

Count client:

_countNew_B_LFY = 
var _t=FILTER('Table',[_isNew_LFY]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id
_countNew_FYTD = 
var _t=FILTER('Table',[_isNew_FYTD]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id
_countNew_LastMonth = 
var _t=FILTER('Table',[_isNew_lastMonth]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return IF(_id=BLANK(),0,_id)
_countNew_thisMonth = 
var _t=FILTER('Table',[_isNew_thisMonth]=1)
var _id=COUNTROWS(SUMMARIZE(_t,[Client_id]))
return _id

So the result:

vangzhengmsft_5-1631171102056.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

How to get your questions answered quickly -- How to provide sample data
W25_Measures for distinct New clients - Various Finanical Year Time intelligence.pbix
Message 4 of 7
325 Views
Anonymous
Not applicable
In response to v-angzheng-msft

‎09-09-2021 11:29 PM

@v-angzheng-msft thank you! Really appreciate your efforts to help. Would you also please let me know how to create measures for current Quarter & last Quarter new Clients?

Message 5 of 7
298 Views
0
v-angzheng-msft
Community Support
Community Support
In response to Anonymous

‎09-10-2021 12:12 AM

Hi, @Anonymous 

 

Glad to see your feedback.

As you can see from the above measures, this problem does take me some time. I will try this challenge further. It may also take some time. If I get the answer, I will reply here.

In addition, if my answer above solves your problem, then please consider Accept it as the solution, it means a lot to me.

 

Best Regards,
Community Support Team _ Zeon Zheng

How to get your questions answered quickly -- How to provide sample data
Message 6 of 7
277 Views
amitchandak
Super User
Super User

‎09-06-2021 09:03 PM

@Anonymous , examples of calculation based on today. Use your measure

 

MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )


LMTD =
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

 

 

QTD Today=
var _month = mod(month(Today()),3)
var _min = date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

 

 

LQTD Today=
var _month = mod(month(Today()),3)
var _min = eomonth(date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1),-4)+1
var _day = date(year(today()), month(today()) -3 , day(today()))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

 

 

YTD Today=
var _min = if(month(today()) >=7, date(year(today()),7,1), date(year(today())-1,7,1) )
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = if(month(today()) >=7,date(year(today())-1,7,1) ,date(year(today())-2,7,1))
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))



Message 2 of 7
404 Views
0
Anonymous
Not applicable
In response to amitchandak

‎09-06-2021 10:09 PM

@amitchandak thanks, but these are only time measures.

Most of these measures do not work with my financial year requirement.

Would you let me know a measure for 'distinct new clients' that I can use in these time measures?

Message 3 of 7
383 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All