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'm trying to display Volume Last month for my custom fiscal year (I already have the table linked, however it's not working.
This is the formula:
and this is the formula:
and this is the formula:
Solved! Go to Solution.
Sorry, try this:
Volume LM2 =
VAR varCurrentMonth =
MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
CALCULATE(
MAX( 'Fiscal Year Conversion'[YearMonth] ),
'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
)
VAR Result =
CALCULATE(
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
'Fiscal Year Conversion'[YearMonth] = varPreviousMonth
)
RETURN
Result
I am doing this without any data or model. If you need further help, please provide data, ideally a PBIX file via a share service (dropbox, onedrive, etc) that has no confidential data.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMy appologies for not providing the data, I will do so next time I have a question.
It works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You've saved me a headache thank you thank you thank you thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! @edhans
Your fiscal calendar conversion must be marked as a date table. Also, if it is not a standard calendar, or a standard calendar that ends on a standard quarter (Mar 31, Jun 30, Sep 30, Dec 31) then you cannot use the built in time intelligence functions. You will have to roll your own function. For example:
Volume LM2 =
VAR varCurrentMonth =
MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
CALCULATE(
MAX( 'Fiscal Year Conversion'[YearMonth] ),
'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
)
VAR Result =
CALCULATE(
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
varPreviousMonth
)
RETURN
Result
The YearMonth field I made up here is a 6 digit integer. 202001 for Jan 2020, 202002 for Feb 2020, etc. You'd need that column, which is simple in Power Query or DAX. You just add a column of the Year * 100 + the month.
Now I can walk up and down those custom months by simply finding the max month where the yearmonth is less than the current yearmonth.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSo I tried that and I got "the end of the input was reached" for Volume LM2.
Your completely right tho! Can you help me take it home please? @edhans
@edhans sorry, it's actually saying "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column"
I just had an extra ] last one but fixed it and after putting exactly this:
Sorry, try this:
Volume LM2 =
VAR varCurrentMonth =
MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
CALCULATE(
MAX( 'Fiscal Year Conversion'[YearMonth] ),
'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
)
VAR Result =
CALCULATE(
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
'Fiscal Year Conversion'[YearMonth] = varPreviousMonth
)
RETURN
Result
I am doing this without any data or model. If you need further help, please provide data, ideally a PBIX file via a share service (dropbox, onedrive, etc) that has no confidential data.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans So now that I have it sorted like this, How do I put in the fiscal month names instead of the 6 digit number of "YearMonth?"
It seems when I try to drag in month names the volume LM dissappears...
Thank you so much for your help you have no idea how much I appreciate you
As long as this is a date table, you can keep whatever you want in the visual as long as it is at the same granularity of the YearMonth field we are working off of. If there is a year and month in the visual, then the filter context will return one YYYYMM value, and that is what the measure needs.
But again, with no images, no data, no image of the model, what you are asking me to do is like calling your mechanic over the phone and saying "my car is going RRrrrrRRRrmmRRRRkkkkk. Please tell me how to fix it."
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry, So this Is what I get:
Also for some reason at the start of the fiscal year, it's not returning the previous month volume.
If I try and add Fiscal Month then I get this:
Also, any idea how to get the grand total to display at the bottom? I've tried SUmx
The total is different. You need something like the following for your measure:
Volume LM2 =
VAR varCurrentMonth =
MAX( 'Fiscal Year Conversion'[YearMonth] )
VAR varPreviousMonth =
CALCULATE(
MAX( 'Fiscal Year Conversion'[YearMonth] ),
'Fiscal Year Conversion'[YearMonth] < varCurrentMonth
)
VAR varMonthlyTotal =
CALCULATE(
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] ),
'Fiscal Year Conversion'[YearMonth] = varPreviousMonth
)
VAR Result =
IF(
ISINSCOPE( 'Fiscal Year Conversion'[FY] ),
varMonthlyTotal,
SUM( 'Opportunity Tracker 2.0'[Annual Volume LBS] )
)
RETURN
Result
At the total line, the year will not be "in scope" because it isn't visible, so it will just sum the annual volume.
As for the other issues, without an image of the model and a better understanding of your date table, I cannot say specifically.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.