cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Measure in calculating the Grand Total on Each Row on a Specific Months.

Hello,

Is the column C possible to replicate on a Measure? The 85 is the total sum of 3 months (april, may, june). I just wanted to get the total value of a specific months.

Just comment out if you need more details.
Thanks!

2 ACCEPTED SOLUTIONS
Super User

Hi @EvanGetsItDone
Then modify it into 2 steps:

1. As you don't have a date column and your months are text, add a month number column:

MonthN = SWITCH('Table'[Month],"jan",1,
"feb",2,
"mar",3,
"apr",4,
"may",5,
"jun",6,
"jul",7,
"Aug",8,
"Sep",9,
"oct",10,
"Nov",11,
"Dec",12,
0)

2. Modify First Dax based on this column :

C = CALCULATE(SUM('Table'[B]),
FILTER('Table','Table'[MonthN]=month(today()) || 'Table'[MonthN]=month(today())-1 || 'Table'[MonthN]=month(today())-2))

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Create calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write this measure

Measure = calculate([sales],datesbetween(calendar[date],edate(eomonth(today(),-1)+1,-2),eomonth(today(),0)))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Super User

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Create calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write this measure

Measure = calculate([sales],datesbetween(calendar[date],edate(eomonth(today(),-1)+1,-2),eomonth(today(),0)))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi @EvanGetsItDone
You can add a calculated column with a formula :

C = CALCULATE(SUM('Table'[B]),
FILTER('Table','Table'[Month]="Apr" || 'Table'[Month]="May" || 'Table'[Month]="Jun"))

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
Helper I

Hello,
Thanks for the response!
Sorry I may not have mentioned it. But I actually need the 3 months (including current month). It doesn't have to be a constant april, may, and june. I need a measure that would calculate the current month and the last 2 months.

Thanks!

Super User

Hi @EvanGetsItDone
Then modify it into 2 steps:

1. As you don't have a date column and your months are text, add a month number column:

MonthN = SWITCH('Table'[Month],"jan",1,
"feb",2,
"mar",3,
"apr",4,
"may",5,
"jun",6,
"jul",7,
"Aug",8,
"Sep",9,
"oct",10,
"Nov",11,
"Dec",12,
0)

2. Modify First Dax based on this column :

C = CALCULATE(SUM('Table'[B]),
FILTER('Table','Table'[MonthN]=month(today()) || 'Table'[MonthN]=month(today())-1 || 'Table'[MonthN]=month(today())-2))

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.