Forum Discussion

visittokiran's avatar
visittokiran
Frequent Visitor
5 years ago
Solved

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

 

Need Help
Tips and Tricks
Tutorial Requests
  • samdthompson's avatar
    samdthompson
    5 years ago

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

     

     

  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

3 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    5 years ago

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

     

     

  • visittokiran's avatar
    visittokiran
    Frequent Visitor
    5 years ago

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

    • v-yingjl's avatar
      v-yingjl
      Icon for Community Support rankCommunity Support
      5 years ago

      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.