3 questions

[joey7504]

hi Everybody

Merry Christmas and Happy Year New!

I encountered with 3 problems during making BI report,

I wonder whether I can get guidiance and advice from you.  

thanks inadvance

Question 1

whether a measure can be used as a value of the filter?  

I need to output data once a month. Every time I have to manually change the filters.  Please refer to enclosed “question 1” png.

Filters on all pages=> value of FYM is 20198 （data of period 8）

If I can use measure “ current fiscal period” as filter FYM’s value,  I would have a dynamic report

Question 2

How to find a date among the data base automatically?

Background:

A module can be repaired serval times, I have to look out the date previously repaired and fill in the current period report.

Image question2-1 is the data base which listed out module’s repair records.

For example  

This month is period 8 of 2019,  IF DATE REVEICED of Unit SN+SERVIAL NUMBER IN is 11-15-2019( please refer to image queston2-1)

and this Unit SN+SERVIAL NUMBER IN has another record earlier than 11-15-2019 , I have to screen out the nearest date and

fill it into the report (please refer to image question2-2)

Question 3

How to change the position/order of the columns?

For example

Move column DATE RECEIVED next to column Unit SN.

If the column is a calculated column, I can not find it when I edit queies.

[v-juanli-msft]

Hi @joey7504 

Question 3 is a limitation currently.

Here is an idea you could vote for.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9467625-moving-rearranging-calcul...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @joey7504 

Create a calcualted column and use in the page level filter.

filter pages = IF(MONTH('Table'[date])= MONTH(TODAY())&&YEAR('Table'[date])=YEAR(TODAY()),1,0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joey7504]

hi maggie (1)thank for your guidance and the illustration data. referring to question 1 , I have tried the formula received from Jef (same as yrs) before , it does work. i tried to reply to Jef few days ago, but when i clicked "POST" button, everything gone. it seemed that message lost due to network delay. (2)the problem remained uncleared are question 2 & 3 i'd like to enclose an image to make further explaination to my question 2, but i don't know the method. (3)do you know the way how to drag a column and move its position? not like excel you can cut and paste the column as you like, if you 'd like to check the cell , you always have to drag the mouse left and right, it's so tied. and a caculated column is not hided when you open edit qurey. you can not find any calculated column there. could you please give me your adivce ? thank you joey

[v-juanli-msft]

Hi @joey7504 

Calculated columns don't exsit in Edit queries by design.

You could upload picture by clicking on camara icon below.

Or you could upload pictures/files onto OneDrive, then share the link here.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joey7504]

thank you maggie,  by the way, how to change the below formular and filter pages of previous month?

/filter pages = IF(MONTH('Table'[date])= MONTH(TODAY())&&YEAR('Table'[date])=YEAR(TODAY()),1,0)/

waiting for your guidiance 

joey

[v-juanli-msft · Accepted solution]

Hi @joey7504 

Create a column

filter pages = DATEDIFF([date],TODAY(),MONTH)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joey7504]

hi Maggie: 

referring to my question no.2 ， i enclosed 2 images to explain my needs,

if a module was repaired before , return the date of last time repair. if no return blank,

example: 

1st repair : 02-22-2019 

2nd repair: 05-16-2019

3rd repair: 07-01-2019

when in july i need return 05-16-2019

[v-juanli-msft]

Hi @joey7504 

Create a new table

date = CALENDARAUTO()

No relationship for this date table

Create a measure

Measure =
CALCULATE (
    MAX ( 'Table'[date repaired] ),
    FILTER (
        'Table',
        'Table'[module] = MAX ( 'Table'[module] )
            && DATEDIFF ( [date repaired], MAX ( 'date'[Date] ), MONTH ) >= 1
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[joey7504]

thank you maggie, 

it's really cool and simple to solve this problem by using DAX datediff,

it ring my bell, in excel , there is a function datedif 

i will remember it now

thank you

[v-juanli-msft]

Hi @joey7504 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Hi @joey7504 ,

For question 1:
Create a custom column in your date table, where you check if the date is in the current month, something like this:

Current YTD = if(MONTH('Calendar'[Date]) = MONTH(NOW()) && 'Calendar'[Year] = YEAR(NOW()) && [Date] <= NOW(),1,0)

Now you can filter on this custom column (where Current YTD = 1)

Question 2:

If I understand your question correctly, you want an extra colum with the MAX date received, grouped by Serial Number In:

You can create the column like this:

LastRepair = CALCULATE(MAX('Table'[DATE RECEIVED]), ALLEXCEPT('Table', 'Table'[SERIAL NUMBER IN]))

Question 3:

I don't know if this is possible.

[joey7504]

hi Jef:

in DEC i tried your formular and got previous month's data, but this month i failed.  1-1=0 ,

actually i need previous month's data, because report alwasy reflect last month situation. could you please help? 

Current YTD = if(MONTH('Calendar'[Date]) = MONTH(NOW()) -1 && 'Calendar'[Year] = YEAR(NOW()) && [Date] <= NOW(),1,0)

waiting for your reply

regards

Joey

[joey7504]

hi Jef thanks for your help. and sorry for my late reply. (1)your answer to question 1 is perfect. problem cleared. (2)how to post an image in the message body when i reply you ? i'd like make a further explaination to my question no.2, but failed. thank you