Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have simple data as follow:
DATE ENTY TYPE DIVISION
1/04/2019 HTCF DIV1
1/04/2019 HTEF DIV2
.
.
.
29/11/2020 HTCF DIV 1
I way trying to calculate sum of enty type till today each year using if logic as follow
HTCF YTD = IF (MONTH[DATE] <= MONTH(TODAY()), IF DAY[DATE] <= DAY(TODAY()), IF ([ENTY TYPE] = "HTCF" , 1 ,0)
THIS LOGIC NOT WORKING
I WANT OUT PUT IN CHART COMPARING LAST YEAR COUNT WITH CURRENT FISCAL YEAR.
THANKS IN ADVA
Solved! Go to Solution.
you need to write two measures in DAX (I am assuming you have a date table with a calendar date field called 'Date'[Date]):
1. HTFC YTD = TOTALYTD( SUM( HTCF ),'Date'[Date],"30/9") - where "30/9" is your year end date
2. HTFC PYTD = CALCULATE( [HTFC YTD], SAMEPERIODLASTYEAR( 'Date'[Date] ))
Hi @visittokiran ,
Based on your description, you can create these measures:
LAST =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ENTRY TYPE] = "HTCF"
&& YEAR ( 'Table'[DATE] )
= YEAR ( TODAY () ) - 1
&& MONTH ( 'Table'[DATE] ) <= MONTH ( TODAY () )
)
)
CURRENT =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[ENTRY TYPE] = "HTCF"
&& MONTH ( 'Table'[DATE] ) <= MONTH ( TODAY () )
&& YEAR ( 'Table'[DATE] ) = YEAR ( TODAY () )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply.
i want explain in detail what I want my output should look like.
These data will be fetched from my server.
I want whenever some one login to this dashboard he should get bar chart showin caparison of count of HTCF for
exactly same period of last year (i.e if i am looking today it should compair data from 1/04/2019 to 30/11/2019 with 1/04/202 to 30/11/2020.)
screen shot of my data ..
Thanks in advance...
Hi @visittokiran ,
Based on your description, you can create these measures:
LAST =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[ENTRY TYPE] = "HTCF"
&& YEAR ( 'Table'[DATE] )
= YEAR ( TODAY () ) - 1
&& MONTH ( 'Table'[DATE] ) <= MONTH ( TODAY () )
)
)
CURRENT =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[ENTRY TYPE] = "HTCF"
&& MONTH ( 'Table'[DATE] ) <= MONTH ( TODAY () )
&& YEAR ( 'Table'[DATE] ) = YEAR ( TODAY () )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you need to write two measures in DAX (I am assuming you have a date table with a calendar date field called 'Date'[Date]):
1. HTFC YTD = TOTALYTD( SUM( HTCF ),'Date'[Date],"30/9") - where "30/9" is your year end date
2. HTFC PYTD = CALCULATE( [HTFC YTD], SAMEPERIODLASTYEAR( 'Date'[Date] ))