Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
visittokiran
Frequent Visitor

YTD CHART FOR MULTIPLE YEARS COMPARISON TILL DAY OF TODAY

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

 

2 ACCEPTED SOLUTIONS
samdthompson
Memorable Member
Memorable Member

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] ))

 

 

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

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 () )
    )
)

CL.png

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.

View solution in original post

3 REPLIES 3
visittokiran
Frequent Visitor

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.)

visittokiran_0-1606708850865.png

 

screen shot of my data ..

visittokiran_1-1606708925579.png

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 () )
    )
)

CL.png

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.

samdthompson
Memorable Member
Memorable Member

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] ))

 

 

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors