topic Re: Calculate values for each month based on a date/time column in DAX Commands and Tips

Calculate values for each month based on a date/time column

Anonymous — Wed, 22 Jul 2020 17:54:38 GMT

How can I create a measure to calculate 'PPV= (Sum of TRUE COL for June 2020)/(Sum of TRUE COL for June 2020+ Sum of FALSE COL for June 2020)'?. June 2020 is just an example. The formula needs to be applied to all the date/time and PPV should be different for different months and years. I need to visualize this calculated PPV to show changes every month and year.

Screenshot of the data below.

Re: Calculate values for each month based on a date/time column

nandic — Wed, 22 Jul 2020 19:15:05 GMT

Hi Anonymous ,

This formula is dynamic and should work for all periods selected (one month, several months, several years).

PPV =

var TrueCol = SUM(Sheet1[True Col])

var FalseCol = SUM(Sheet1[False Col])

var Total = TrueCol + FalseCol

RETURN

DIVIDE(TrueCol,Total)

Cheers,

Nemanja

Re: Calculate values for each month based on a date/time column

amitchandak — Thu, 23 Jul 2020 02:59:02 GMT

Anonymous , Try a measure like this

divide(sum(Table[True Col]),sum(Table[True Col]) + sum(Table[False Col]))

For date filter better join to date filter an use that.

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.