Forum Discussion

Unicorn_Tech's avatar
Unicorn_Tech
Icon for Resolver I rankResolver I
5 years ago
Solved

License information according to time

Hi.  I hope there is a PowerBI solution to this.

 

I have a client in Australia.  This client has a license for 5 hectares, signed in 2015.  In 2018, he adds another 5 hectares to his total, giving him 10 hectares altogether.

In the CRM the first record is entered for 5 hectares in 2015.  In 2018, the old record is deactivated and a new record of 10 hectares is added.

 

A powerBI visual column chart can consider both the deactivated 5 hectares, and the active 10 hectares, if I wanted to show a yearly progression.  Fine.

The problem is then zooming out to the country as a whole.  Technically, this grower had 5 hectares in 2015, 5 in 2016, 5 in 2017 and 10 in 2018.  When I include him in a country column chart, his info is only there for 2015 and 2018.  I would like his figures to be there for 2015-2018.

Is there a way PowerBI can do calculations licensing situations?  Thank you.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Unicorn_Tech ，

     

    First create a date table as below:

    Date table = CALENDAR(MIN('Table'[Amendment signed date]),TODAY())

    Then create a measure as below:(suppose there is no end date for the record in 2018)

    Measure = 
var _enddate=IF(ISBLANK(MAX('Table'[ Amendment end date])),TODAY(),MAX('Table'[ Amendment end date]))
Return
CALCULATE(MAX('Table'[ Hectares]),FILTER(ALL('Table'),'Table'[Amendment signed date]<=MAX('Date table'[Date])&&_enddate>=MAX('Date table'[Date])))

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

      • Unicorn_Tech's avatar
        Unicorn_Tech
        Icon for Resolver I rankResolver I
        5 years ago

        So basically I'm trying to get a bar graph out of this, that shows values for data between the start and end date.  If there is no end date, I expect it to be = today.

        And I want to be able to coallate it per country as well.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Unicorn_Tech ，

     

    First create a date table as below:

    Date table = CALENDAR(MIN('Table'[Amendment signed date]),TODAY())

    Then create a measure as below:(suppose there is no end date for the record in 2018)

    Measure = 
var _enddate=IF(ISBLANK(MAX('Table'[ Amendment end date])),TODAY(),MAX('Table'[ Amendment end date]))
Return
CALCULATE(MAX('Table'[ Hectares]),FILTER(ALL('Table'),'Table'[Amendment signed date]<=MAX('Date table'[Date])&&_enddate>=MAX('Date table'[Date])))

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!