Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

3 questions

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.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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.

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    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
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

        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.