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.
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] ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.