Forum Discussion
How to declare a variable?
Hi v-yulgu-msft,
Thanks for the article i will keep this in mind before posting next time.
Actully if i talking about the scenario, I have to compare YOY and for that then i have to apply the filter of financial year.
And when we applied the filters the data get shorted according to that but the "CARD" chart is fix for only one measure
and it show only value that was calculated.
And if i want to see the comparison of two different year which i select through filter then the "CARD" should show the result depending on the filter selection.
So for that i need two variable where i can store the filter values and remainning calculation will be done according to the years stored in the variables.
Currenty i just fixed the card with a two years (can say i just made it static) and when i select random value then it show me null.
Regards,
Satish Kumar
Hi Satish_Kumar,
Below is a simple example in my test.
Suppose the simple table structure looks like.
Create a calculated table which lists unique FY values.
New Table = VALUES(Table[FY]) Year Number = RIGHT(New Table[FY],2) Last FY = LOOKUPVALUE(New Table[FY],New Table[Year Number],New Table[Year Number]-1)
Then, create measures.
Current year value = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[FY], 1 ) ) ) Last Year = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[Last FY], 1 ) ) ) YOY = ([Current year value]-[Last Year])/[Last Year]
Add 'New Table'[FY] into slicer.
Best regards,
Yuliana Gu
I tried the solution given by you but some where i failed.
Take a look and guide me where i did the mistake.
Regards,
Satish Kumar
Hi Satish_Kumar,
From current information, it's hard to say why it doesn't return desired result. Please check the measures for [current year value] and [last year value] in my original post, especially the last year:
Last Year = CALCULATE ( SUM ( Table[Sales] ), FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[Last FY], 1 ) ) )If possible, please share your .pbix file for further analysis.
Regards,
Yuliana GuHi v-yulgu-msft
here the link for my PBIX file.
https://drive.google.com/file/d/0Bxq6NdcHyxydck0taTBzNGtGT28/view?usp=sharing
Regards,
Satish Kumar
Hi Satish_Kumar,
I have made some adjustment in your .pbix file.
Remove the relationship between two tables.
Pay attention to the content in bold.
Current year value = CALCULATE ( SUM ( Sales[Sale] ), FILTER ( Sales, Sales[FinancialYearDisplay] = LASTNONBLANK ( 'New Table'[FinancialYearDisplay], 1 ) ) )
Last Year =
CALCULATE (
SUM ( Sales[Sale] ),
FILTER (
Sales,
Sales[FinancialYearDisplay] = LASTNONBLANK ( 'New Table'[Last FY], 1 )
)
)Regards,
Yuliana GuSorry for Replying Late......
The code which you send me is very much helpful.
Can I create a new table with more than one value used?
Like we did in New Table = VALUES(Sales[FinancialYearDisplay]).
I need this because I have to use month and quarters.
Hi Satish_Kumar,
If it is a date column, you can use the same formula to create a new table:
New Table = VALUES(Sales[date])
Then, you could get month and quarter values from this date column.
Month=New Table[date].month
Month=New Table[date].quarter
Regards,
Yuliana GuAre you saying that we can just pass only one value parameter for creating a new table?
Regards,
Satish
Hi v-yulgu-msft
The solution which was provided by you is very helpful to me.
But please refer to my screen shot where I can create only one filter of the financial year and this filter must be applied to the remaining fields say: Revenue, Gross Margin, PBT, Profit etc.
Currently showing default values only
So just because of this conditions I just want to create a table in which I can put multiple fields together to make my report.
And to control all the graph by single or more filters.
Regards,
Satish Kumar
Hi Satish_Kumar,
No. For Values function, it only returns one column with unique values in a new table. If you need multiple columns, you can create calculated column into this new table. Or please refer to SELECTCOLUMNS function.
Regards,
Yuliana GuHi v-yulgu-msft
Thanks for your kind suggestion!!
Can we put multiple filters on the current and last year value?
Say if I applied a filter of a financial year(FY15-16) and I am willing to watch the comparison of Jan Month of current year i.e. (FY15-16) and (FY14-15) in the same card.
And if this is possible then this will help me to a very large extent because I am trying to apply month and quarter filter on the same card and I just want to see the card value change according to the filter applied like the drill down.
Regards,
Satish Kumar
Hi Satish_Kumar,
Yes, we can put multiple filters on the measure. The logic is the same. Suppose you already have Month column and Quarter column in both source table and calculate table.
For example,
Current year value =
CALCULATE (
SUM ( Table[Sales] ),
FILTER ( Table, Table[FY] = LASTNONBLANK ( New Table[FY], 1 ) && Table[Quarter]=LASTNONBLANK(New Table[Quarter,1]) )
)You said "see the card value change according to the filter applied like the drill down", do you mean if you only apply filters to Year slicer, while don't select any value in Month and Quarter slicer, the card shoud display the total value for whole year? If you apply filters to Year slicer and Quarter slicer, the card visual should display total value for this specific Quarter?
If so, maybe you could try this:
Current year vCurrent year value = IF ( ISFILTERED ( 'New Table'[Quarter] ) = TRUE () && ISFILTERED ( 'New Table'[Month] ) = FALSE (), CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( Table, 'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 ) && 'Table'[Quarter] = LASTNONBLANK ( 'New Table'[Quarter], 1 ) ) ), IF ( ( ISFILTERED ( 'New Table'[Quarter] ) = TRUE () && ISFILTERED ( 'New Table'[Month] ) = TRUE () ), CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( Table, 'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 ) && 'Table'[Quarter] = LASTNONBLANK ( 'New Table'[Quarter], 1 ) && 'Table'[Month] = LASTNONBLANK ( 'New Table'[Month], 1 ) ) ), CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( Table, 'Table'[FY] = LASTNONBLANK ( 'New Table'[FY], 1 ) ) ) ) )
Regards,
Yuliana Gu