Forum Discussion
Create a Measure without filters
Hello everyone,
my question is very simple: I have a budget table, as shown below, that contains a budget value for each sales agent, for each customer, for each month of the 2021.
I would like to compare sales with budget by quarter.
However, I would like to have a measure that considers the amount budgeted per quarter regardless of the month we are in.
For example, we are now in May, which is in the second quarter. I want to compare second quarter sales (to date) against the total budget for the entire second quarter.
How could I calculate that measure?
Thank you so much in advance!!
| ID_Sales Agent | ID_Customer | Month | Budget |
| 001 | AAA | Jan 2021 | € 1,000 |
| 001 | BBB | Jan 2021 | € 2,500 |
| 002 | CCC | Feb 2021 | € 3,500 |
Anonymous , With help from time intelligence and date table, create a measure like
This will give full qtr value
Qtr Sales = CALCULATE(SUM(Budget [Budget Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))
Also consider - closingbalancequarter -https://www.youtube.com/watch?v=yPQ9UV37LOU
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi, Anonymous
According to your description, I can roughly understand your requirement, and I created some test data and two measures to achieve your requirement, you can take a look at my steps:
This is my test data:
- I created a calculated column like this:
Quater = QUARTER([Date])- I created two measures:
quarter sales till today = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),[Quater]=QUARTER(TODAY())&&[Date]<=TODAY()))total budget for the entire quarter = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),[Quater]=QUARTER(TODAY())))- Then I created two card charts to place the two measures and set the “Display unit” as “None” to get the specific number:
And you can get what you want.
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , With help from time intelligence and date table, create a measure like
This will give full qtr value
Qtr Sales = CALCULATE(SUM(Budget [Budget Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))
Also consider - closingbalancequarter -https://www.youtube.com/watch?v=yPQ9UV37LOU
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi, Anonymous
According to your description, I can roughly understand your requirement, and I created some test data and two measures to achieve your requirement, you can take a look at my steps:
This is my test data:
- I created a calculated column like this:
Quater = QUARTER([Date])- I created two measures:
quarter sales till today = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),[Quater]=QUARTER(TODAY())&&[Date]<=TODAY()))total budget for the entire quarter = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),[Quater]=QUARTER(TODAY())))- Then I created two card charts to place the two measures and set the “Display unit” as “None” to get the specific number:
And you can get what you want.
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.