Forum Discussion

rajanimaddala's avatar
rajanimaddala
Helper II
6 years ago
Solved

PERIODLASTYEAR row count

Hi,

I have 2 tables

1. Enrolments

2. Calendar

 

Both are related via Enrolments[Transaction Date] -> Calendar[Date]

I have a measure to count number of rows.

Total Enrolments = COUNTROWS(Enrolments[Id])

It is working well.

 

I have a slicer (FY, Month)

I need to find Total Enrolments for last year same month. The DAX expressions is as below

 

Total Enrolments Last Year =

var CurrentMonthStartDate = MIN(Enrolments[Transaction Date])

var LastYearStartDate = DATE(YEAR(CurrentMonthStartDate)-1,MONTH(CurrentMonthStartDate),1)

RETURN CALCULATE([Total Enrolments], FILTER(ALL(Enrolments), Enrolments[Transaction Date]>=LastYearStartDate && Enrolments[Transaction Date]<=EOMONTH(LastYearStartDate,0))

 

This is working okay with normal Clustered chart.

 

I have added WeekNo in the chart's x-axis. Changed the DAX as below

Total Enrolments Last Year =

var CurrentMonthStartDate = MIN(Enrolments[Transaction Date])

var LastYearStartDate = DATE(YEAR(CurrentMonthStartDate)-1,MONTH(CurrentMonthStartDate),1)

RETURN CALCULATE([Total Enrolments], FILTER(ALL(Enrolments), Enrolments[Transaction Date]>=LastYearStartDate && Enrolments[Transaction Date]<=EOMONTH(LastYearStartDate,0) && related(Calendar[Date]) IN VALUES(Calendar[Date]))

 

I don't know why I am getting less value than it should be

 

What is the issue? I can't understand

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    You can try functions like

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can try functions like

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi rajanimaddala ,

    Could you please provide some sample data(exclude sensitive data) in table Enrolments in order to make troubleshooting? You referred that you get less data than it should actually get  after changed the formula. Could you please provide a comparative screenshot?

    Best Regards

    Rena