Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Request help

Dear All,   I request your help to solve a scenario. I have a input table, using that I need to create a output report .I am having trouble in creating the dax formulas for columns Shares Count .Th...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    Hi Anonymous,

    Based on my test, you could refer to below steps:

    1.Create relationship between the two tables.

    2.Create measures:

    2006 = VAR myDate = DATE(2006, 07, 07)
    RETURN
    CALCULATE(SUM(Table2[SharesCount]),
    FILTER('Table2',myDate>='Table2'[ValidFrom] && myDate<=Table2[ValidTo]),FILTER(CS,
    CS[EntryDate] <= myDate && ( CS[ExitDate] >= mydate || CS[ExitDate] = BLANK())))

     

    2005 = VAR myDate = DATE(2005, 07, 07)
    RETURN
    CALCULATE(SUM(Table2[SharesCount]),
    FILTER('Table2',myDate>='Table2'[ValidFrom] && myDate<=Table2[ValidTo]),FILTER(CS,
    CS[EntryDate] <= myDate && ( CS[ExitDate] >= mydate || CS[ExitDate] = BLANK())))

     

    Difference=[2006]-[2005]

    3.Create a Table visual and add related fields, now you can see the result.

    You can also download the PBIX file to have a view.

    https://www.dropbox.com/s/0nqh0ayxz840dru/Request%20help.pbix?dl=0

     

    Regards,

    Daniel He

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous

 

Create a Calendar table if its not there aleady in your model and make the proper relationship.

 

Create a slicer based on the Date column from Calendar table.

 

Assign this date to your variable.

 

VAR myDate = SELECTEDVALUE( Calendar_date[Date])

 

This way the user can select the date and this will be passed dynamically.

 

Thanks
Raj