Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've tried searching this topic (quite common) and have tried all the recommended solutions and am still stuck...hoping someone can help me think of something I'm clearly missing....
Yet I still end up with this:
Any ideas?
I've got the rest of the relationships built just struggling with the Calendar Table/functionality.
Not sure if it matters, but the Calendar Table was built off of a Query, but I've ensured it's the correct format / Date --> Years are correct.
Solved! Go to Solution.
Use these measures:
Gross Billings $ FY 2018 = CALCULATE( [Gross Billings $], FILTER('Calendar','Calendar'[Year] = 2018) )
And for the MTD:
Gross Billings $ FY18 MTD = CALCULATE( [Gross Billings $], DATESMTD('Calendar'[Date]), FILTER('Calendar','Calendar'[Year] = 2018) )
The reason it works that way is when you use a simple filter in CALCULATE as you have, it wraps an ALL() around it. Read this SQLBI article on how simple filters in CALCULATE work.
The crux of it is your measure:
Test = CALCULATE( [Gross Billings $], 'Calendar'[Year] = 2018 )
is rewritten as this by DAX:
Test = CALCULATE( [Gross Billings $], FILTER( ALL('Calendar'[Year]), 'Calendar'[Year] = 2018 ) )
So viewed that way, it is saying ignore the filter context from the matrix columns. Don't use just 2016 dates for column 2016, but use all dates, but then only show me 2018 from that. Then it does the same for 2017, 2018, 2019, etc. So only 2018 looks right to you.
I rarely use a simple filter in Calculate. I almost always use FILTER() there to ensure I have more control over what I get.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingWhat is wrong with the output? It seems to be breaking things out by year, albiet with test data ($10/yr).
One question on the date table, it must have ALL dates in it. If it is built directly off of your invoice table, and you didn't bill every day, that will cause problems. You should have a start date, and a list of contiguious dates for however long you need it.
For example, in Power Query, this will give you a list of dates from Jan 1, 2016 through Dec 31, 2018 assuming you are running this in 2018. It uses the system clock.
let Source = #date(Date.Year(DateTime.LocalNow())-2,1,1), DatesAsAList = List.Dates(Source,Number.From(Date.EndOfYear(#date(Date.Year(DateTime.LocalNow())+1,1,1)))-Number.From(Source)+1,#duration(1,0,0,0))
in
DatesAsList
Also make sure both dates (calendar table and invoice table) are the same - Date, Date/Time, or Date/Time/Timezone. It cannot be a mix. You cannot successfully join Date to a Date/Time field.
If none of the above is helpful, please explain what you expect your output to be.
And post your Gross Billings $ measure. It shouldn't be more than SUM(Sales[Billings]) though unelss you have other logic in there for some reason.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingedhans,
#1)
Date Table has everything in it from 2015 until 2025+, day by day.
#2) This is what I see, sorry it's dummy data. But I hope it depicts what I'm seeing.
https://i.imgur.com/zaFN3iZ.png
Basically, the correct figure is showing up, but it's showing up even in Fiscal Years its not supposed to (red boxes = doesn't belong)
Yes, you are correct. Gross Billings ($) = sum(Sales[Billings])
Gross Billings ($) FY15 = calculate(Gross Billings ($),'Calendar Table'[Fiscal Year]=2015)
Gross Billings ($) FY16 = calculate(Gross Billings ($),'Calendar Table'[Fiscal Year]=2016)
Gross Billings ($) FY17 = calculate(Gross Billings ($),'Calendar Table'[Fiscal Year]=2017)
etc.
Thanks in advance
@edhans wrote:What is wrong with the output? It seems to be breaking things out by year, albiet with test data ($10/yr).
One question on the date table, it must have ALL dates in it. If it is built directly off of your invoice table, and you didn't bill every day, that will cause problems. You should have a start date, and a list of contiguious dates for however long you need it.
For example, in Power Query, this will give you a list of dates from Jan 1, 2016 through Dec 31, 2018 assuming you are running this in 2018. It uses the system clock.
let Source = #date(Date.Year(DateTime.LocalNow())-2,1,1), DatesAsAList = List.Dates(Source,Number.From(Date.EndOfYear(#date(Date.Year(DateTime.LocalNow())+1,1,1)))-Number.From(Source)+1,#duration(1,0,0,0))
in
DatesAsList
Also make sure both dates (calendar table and invoice table) are the same - Date, Date/Time, or Date/Time/Timezone. It cannot be a mix. You cannot successfully join Date to a Date/Time field.
If none of the above is helpful, please explain what you expect your output to be.
And post your Gross Billings $ measure. It shouldn't be more than SUM(Sales[Billings]) though unelss you have other logic in there for some reason.
Are you sorting anything by the calendar year? If so, you need to also include that in your CALCULATE filter.
Of course, if you just dropped the Gross Billing $ measure in and got rid of the others, the grid should work fine at that point. The date table will allow it to put your data in the right columns without the need for filters.
EDIT: See this file for an example of what I am talking about. This is just one measure.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
@edhans wrote:Are you sorting anything by the calendar year? If so, you need to also include that in your CALCULATE filter.
Of course, if you just dropped the Gross Billing $ measure in and got rid of the others, the grid should work fine at that point. The date table will allow it to put your data in the right columns without the need for filters.
EDIT: See this file for an example of what I am talking about. This is just one measure.
edhans,
Thanks so much for the pbix example.
I took it one step further and recreated the problem I'm seeing in my data, along with the formulas used.
https://i.imgur.com/3HgxNdV.png
Question #1) Shouldn't $6,551 only appear under 2018? Why do I see it across all 10 years? I see it totals properly (it doesn't take $6,551 x 10 years), but still confusing why it shows up 9 extra times.
Question #2) Shouldn't $147 only appear under 2018? Why do I see it across all 10 years? I see it totals properly (it doesn't take $147 x 10 years), but still confusing why it shows up 9 extra times.
Thanks in advance,
Use these measures:
Gross Billings $ FY 2018 = CALCULATE( [Gross Billings $], FILTER('Calendar','Calendar'[Year] = 2018) )
And for the MTD:
Gross Billings $ FY18 MTD = CALCULATE( [Gross Billings $], DATESMTD('Calendar'[Date]), FILTER('Calendar','Calendar'[Year] = 2018) )
The reason it works that way is when you use a simple filter in CALCULATE as you have, it wraps an ALL() around it. Read this SQLBI article on how simple filters in CALCULATE work.
The crux of it is your measure:
Test = CALCULATE( [Gross Billings $], 'Calendar'[Year] = 2018 )
is rewritten as this by DAX:
Test = CALCULATE( [Gross Billings $], FILTER( ALL('Calendar'[Year]), 'Calendar'[Year] = 2018 ) )
So viewed that way, it is saying ignore the filter context from the matrix columns. Don't use just 2016 dates for column 2016, but use all dates, but then only show me 2018 from that. Then it does the same for 2017, 2018, 2019, etc. So only 2018 looks right to you.
I rarely use a simple filter in Calculate. I almost always use FILTER() there to ensure I have more control over what I get.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingedhans,
If I could give you a e-highfive & e-chestbump I would.
Appreciate your sample PBIX, helped me recreate my problem for you.
Thank you very much, you are a gentleman and a scholar.
<michael-scott.gif>
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |