Time Intelligence-By Groups

[AnnyGalvis]

Hi, I hope you all are doing well. 

I will try to be very clear and I hope someone can help me. 

I have a Table with sales and also the area where the sales are from, I need to show the sales of this year until yesterday so I tried using CALCULATE and then add date -1 day, but it does not work when I put the area on the report too, so it just works for a total but I also need the total by area. I don't know what to do, I have the kinda de same problem using SAMEPERIODLASTYEAR when I put the area on the report. 

I need something like:

AREA - Sales until yesterday- Sales Today - Sales SameperiodlastYear - SameDayLastYear

AREA1 -      55.555      -                  1        -           66.000                      -      2

AREA2 -      7.777         -                5         -           70.000                      -      1 

TOTAL:      63.332        -               6          -         136.0000                     -     3 

I appreciate a lot any idea, thank you much. 

[Ashish_Mathur]

Hi,

There should be a Calendar Table (with the last date dynamically extendning to Today's date) with a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  Try this:

Sales until yesterday

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),TODAY()-1))

Sales today

=CALCULATE(SUM(Data[Sales]),LASTDATE(Calendar[Date]))

Do these measures work?

[v-yuta-msft]

@AnnyGalvis ,

Could you share some sample data and clarify more details about your requirement?

Regards,

Jimmy Tao